const express = require("express");
const router = express.Router();
const path = require('path');
const fs = require('fs');
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const moment = require("moment-timezone");
const multer = require("multer");
const axios = require("axios");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");
const config = require("../config");
const { content } = require("googleapis/build/src/apis/content/index.js");
const apiKey = 'FdXdzDJbfkPJWRw2R2fLNgb6cnG201RS';

const util = require('util'); // 引入 util 用于 promisify
const exec = util.promisify(require('child_process').exec); // 将exec函数 Promise化
const mysql = require("mysql");


// 配置 multer 的存储设置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        // 设置文件上传目录
        cb(null, "/home/wwwroot/default/RecipeMedia");
    },
    filename: function (req, file, cb) {
        // 生成文件名
        cb(null, Date.now() + "-" + file.originalname);
    },
});

const upload = multer({ storage: storage });

// 创建文件上传的 POST 接口
router.post("/upload", upload.single("file"), (req, res) => {

    const file = req.file;
    const { pk } = req.body;
    if (!file) {
        return res.status(400).send("请上传一个文件。");
    }
    let fileUrl = ''

    fileUrl = `https://pdf.seamobitech.com/RecipeMedia/${file.filename}`;



    // 返回文件存储地址
    res.send({
        code: 0,
        message: "success",
        fileUrl: fileUrl,
    });
});

// 加密函数
function encrypt(message, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv);
    // 加密模式为CBC，填充模式为PKCS7
    const encrypted = CryptoJS.DES.encrypt(message, keyHex, {
        iv: ivHex,
        mode: CryptoJS.mode.CBC,
        padding: CryptoJS.pad.Pkcs7,
    });
    return encrypted.toString();
}

// 解密函数
function decrypt(ciphertext, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv); // 使用key的前8个字符作为IV
    // 解密模式为CBC，填充模式为PKCS7
    const decrypted = CryptoJS.DES.decrypt(
        {
            ciphertext: CryptoJS.enc.Base64.parse(ciphertext),
        },
        keyHex,
        {
            iv: ivHex,
            mode: CryptoJS.mode.CBC,
            padding: CryptoJS.pad.Pkcs7,
        }
    );
    return decrypted.toString(CryptoJS.enc.Utf8);
}

// 工具函数-获取北京时间
function getNowInBeijing() {
    return moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
}

// 随机邀请码生成
async function generateInviteCode() {
    // 可以用随机数字字母或第三方生成逻辑
    const chars = 'abcdefghijklmnopqrstuvwxyz0123456789';
    let code = '';
    for (let i = 0; i < 6; i++) code += chars[Math.floor(Math.random() * chars.length)];
    // 确认不重复
    let retry = 0;
    let exists = true;
    while (exists && retry < 5) {
        const rows = await mysqlQuery('SELECT 1 FROM Recipe_user WHERE invitation_code = ?', [code]);
        exists = rows.length > 0;
        if (exists) code = chars.split('').sort(() => 0.5 - Math.random()).slice(0, 6).join('');
        retry++;
    }
    return code;
}

// 生成用户名
function generateDefaultUsername() {
    // 6位字母数字
    const chars = 'abcdefghijklmnopqrstuvwxyz0123456789';
    let randStr = '';
    for (let i = 0; i < 6; i++) randStr += chars[Math.floor(Math.random() * chars.length)];
    return `user-${randStr}`;
}

/**
 * 主登录/注册逻辑
 * 
 * @param {string} identifier 
 * @param {'apple'|'email'} type 
 * @returns {user, isNew}
 */
/**
 * 主登录/注册逻辑
 * 
 * @param {string} identifier 
 * @param {'apple'|'email'} type 
 * @param {Object} extraFields  // 新增
 * @returns {user, isNew}
 */
async function checkOrCreateUserByType(identifier, type, extraFields = {}) {
    let fieldName = type === 'apple' ? 'apple_identifier' : 'email_identifier';
    let querySql = `SELECT * FROM Recipe_user WHERE ${fieldName} = ? LIMIT 1`;
    let result = await mysqlQuery(querySql, [identifier]);

    if (result.length > 0) {
        // 老用户，刷新登录时间
        const updateSql = 'UPDATE Recipe_user SET last_login_time = NOW() WHERE user_id = ?';
        await mysqlQuery(updateSql, [result[0].user_id]);
        let user = result[0];
        // 格式化时间
        [
            'registration_time',
            'last_login_time',
            'created_at',
            'updated_at',
            'subscription_start_date',
            'subscription_expiry_date',
            'invitation_time'
        ].forEach(field => {
            user[field] = user[field]
                ? moment(user[field])
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss")
                : null;
        });
        return { user, isNew: false };
    } else {
        // 新用户
        const userId = utils.createId();
        const username = generateDefaultUsername();
        const avatarList = [
            'icon1', 'icon2', 'icon3', 'icon4', 'icon5'
        ];
        const avatarUrl = avatarList[Math.floor(Math.random() * avatarList.length)];
        const now = getNowInBeijing();
        const inviteCode = await generateInviteCode();

        let fields = [
            'user_id', 'auth_type', 'username', 'avatar_url',
            'registration_time', 'last_login_time',
            'created_at', 'updated_at', 'invitation_code'
        ];
        let values = [
            userId, type, username, avatarUrl,
            now, now, now, now, inviteCode
        ];
        // 动态加可选字段
        let insertField = fieldName;
        fields.push(insertField);
        values.push(identifier);

        // 这里插入可选的个性字段（注意只处理非空）
        const extraDbFields = [
            'cuisine',
            'cuisine_dislike',
            'cuisine_left',
            'allergies',
            'dietary_preference',
            'min_servings',
            'max_servings'
        ];
        extraDbFields.forEach(f => {
            if (
                Object.prototype.hasOwnProperty.call(extraFields, f) &&
                extraFields[f] !== null &&
                extraFields[f] !== undefined &&
                extraFields[f] !== ''
            ) {
                // 对 min_servings / max_servings 做数字类型安全处理
                if (f === 'min_servings' || f === 'max_servings') {
                    let val = Number(extraFields[f]);
                    // 只有有效整数才插入
                    if (!isNaN(val)) {
                        fields.push(f);
                        values.push(val);
                    }
                } else {
                    fields.push(f);
                    values.push(extraFields[f]);
                }
            }
        });

        let insertSql = `INSERT INTO Recipe_user (${fields.join(',')}) VALUES (${fields.map(() => "?").join(',')})`;

        await mysqlQuery(insertSql, values);

        // 新用户从库里查出
        let rows = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id = ?', [userId]);
        let user = rows[0];
        // 时间格式化
        [
            'registration_time',
            'last_login_time',
            'created_at',
            'updated_at',
            'subscription_start_date',
            'subscription_expiry_date',
            'invitation_time'
        ].forEach(field => {
            user[field] = user[field]
                ? moment(user[field])
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss")
                : null;
        });
        return { user, isNew: true };
    }
}
router.post('/markSignupFlowFinished', async (req, res) => {
    try {
        const { user_id } = req.body;
        if (!user_id) {
            return res.status(400).json({ success: false, message: "缺少 user_id 参数" });
        }

        // 执行更新
        const sql = `UPDATE Recipe_user SET is_signup_flow_finished = 1 WHERE user_id = ?`;
        const result = await mysqlQuery(sql, [user_id]);

        // 判断是否真的更新到1行（不存在则不报错，但可提示未找到）
        if (result.affectedRows > 0) {
            res.send({ code: 0, message: "已标记为已走完新用户注册流程" });
        } else {
            res.send({ code: 1, message: "未找到该 user_id" });
        }
    } catch (error) {
        console.error('设置注册流程完成状态失败:', error);
        res.status(500).json({ success: false, message: "服务端错误", error: error.message });
    }
});
/**
 * 检查用户是否已存在
 * @param {string} identifier
 * @param {string} type
 * @returns {Promise<boolean>} 是否查到用户
 */
async function checkUserExist(identifier, type) {
    let fieldName = type === 'apple' ? 'apple_identifier' : 'email_identifier';
    let querySql = `SELECT user_id FROM Recipe_user WHERE ${fieldName} = ? LIMIT 1`;
    let result = await mysqlQuery(querySql, [identifier]);
    return result.length > 0;
}
router.post('/check_register', async (req, res) => {
    try {
        const { identifier, type } = req.body;
        if (!identifier || !type) {
            return res.status(400).json({ code: 400, message: "缺少参数" });
        }
        if (!['apple', 'email'].includes(type)) {
            return res.status(400).json({ code: 400, message: "不支持的登录类型" });
        }

        const isRegistered = await checkUserExist(identifier, type);

        res.send({
            code: 0,
            message: 'success',
            isNew: !isRegistered // 没注册过true，注册过false
        });
    } catch (e) {
        console.error('查询注册状态异常:', e);
        res.status(500).json({ code: 500, message: '服务器异常' });
    }
});
// 路由部分
router.post('/login', async (req, res) => {
    try {
        const {
            identifier,
            type,
            cuisine,
            cuisine_dislike,
            cuisine_left,
            allergies,
            dietary_preference,
            min_servings,
            max_servings
        } = req.body;

        if (!identifier || !type) {
            return res.status(400).json({ code: 400, message: "缺少参数" });
        }
        if (!['apple', 'email'].includes(type)) {
            return res.status(400).json({ code: 400, message: "不支持的登录类型" });
        }

        // 将可选字段组装为对象传递
        const extraFields = {
            cuisine,
            cuisine_dislike,
            cuisine_left,
            dietary_preference,
            allergies,
            min_servings,
            max_servings
        };

        const { user, isNew } = await checkOrCreateUserByType(identifier, type, extraFields);

        res.send({
            code: 0,
            message: 'success',
            data: {
                user,
                isNew
            }
        });
    } catch (e) {
        console.error(`登录异常:`, e);
        res.status(500).json({ code: 500, message: '服务器异常' });
    }
});

//  
router.post('/getUserInfo', async (req, res) => {
    try {
        const { user_id } = req.body;

        if (!user_id) {
            return res.status(400).json({ code: 400, message: "user_id必填" });
        }

        const rows = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
        if (!rows || rows.length === 0) {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }
        let user = rows[0];
        // 格式化时间字段
        [
            'registration_time',
            'last_login_time',
            'created_at',
            'updated_at',
            'subscription_start_date',
            'subscription_expiry_date',
            'invitation_time'
        ].forEach(field => {
            user[field] = user[field]
                ? moment(user[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")
                : null;
        });

        return res.send({
            code: 0,
            message: 'success',
            data: user
        });
    } catch (e) {
        console.error('获取用户信息异常:', e);
        res.status(500).json({ code: 500, message: "服务器异常" });
    }
});
//注销账号
router.post('/user/account/delete', async (req, res) => {
    const { user_id } = req.body;
    if (!user_id) {
        return res.status(400).json({ code: 400, msg: 'user_id必传' });
    }
    try {
        // 检查用户是否存在
        const rows = await mysqlQuery('SELECT user_id FROM Recipe_user WHERE user_id = ?', [user_id]);
        if (!rows.length) {
            return res.status(404).json({ code: 404, msg: '账号不存在或已注销' });
        }
        // 注销（物理删除）
        await mysqlQuery('DELETE FROM Recipe_user WHERE user_id = ?', [user_id]);
        // 你可以按需还加上联删其它附属表内的用户数据（如Recipe_UserRecipe、Recipe_Collect等，需级联delete处理）
        return res.send({ code: 0, msg: '账号已注销' });
    } catch (e) {
        console.error('[user/account/delete] 注销失败:', e);
        res.status(500).json({ code: 500, msg: '服务器异常' });
    }
});

// 更新用户个性字段
router.post('/updateUserInfo', async (req, res) => {
    try {
        const {
            user_id,
            cuisine,
            cuisine_dislike,
            cuisine_left,
            allergies,
            dietary_preference,
            min_servings,
            max_servings
        } = req.body;

        if (!user_id) {
            return res.status(400).json({ code: 400, message: "user_id必填" });
        }

        const rows = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
        if (!rows || rows.length === 0) {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }

        // 可被动态更新的字段
        const updatableFields = [
            'cuisine',
            'cuisine_dislike',
            'cuisine_left',
            'allergies',
            'dietary_preference',
            'min_servings',
            'max_servings'
        ];
        let updates = [];
        let values = [];

        updatableFields.forEach(f => {
            if (req.body[f] !== undefined) {
                // 对 min_servings、max_servings 做类型检查
                if (f === 'min_servings' || f === 'max_servings') {
                    // 允许显式传 null 以清空
                    if (req.body[f] === null) {
                        updates.push(`${f} = NULL`);
                    } else {
                        let intValue = Number(req.body[f]);
                        if (!isNaN(intValue)) {
                            updates.push(`${f} = ?`);
                            values.push(intValue);
                        }
                    }
                } else {
                    updates.push(`${f} = ?`);
                    values.push(req.body[f]);
                }
            }
        });

        if (updates.length === 0) {
            return res.status(400).json({ code: 400, message: "无可更新字段" });
        }

        // 最后更新时间
        updates.push(`updated_at = NOW()`);

        let updateSql = `UPDATE Recipe_user SET ${updates.join(', ')} WHERE user_id = ?`;
        values.push(user_id);

        const result = await mysqlQuery(updateSql, values);

        if (result.affectedRows && result.affectedRows > 0) {
            // 返回最新用户数据
            const rows = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
            let user = rows[0];
            // 格式化时间字段
            ['registration_time', 'last_login_time', 'created_at', 'updated_at', 'subscription_start_date', 'subscription_expiry_date', 'invitation_time'].forEach(field => {
                user[field] = user[field] ? moment(user[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss") : null;
            });
            return res.send({
                code: 0,
                message: 'success',
                data: user
            });
        } else {
            return res.status(404).json({ code: 404, message: "用户不存在或未更新" });
        }
    } catch (e) {
        console.error('用户个性字段更新异常:', e);
        res.status(500).json({ code: 500, message: "服务器异常" });
    }
});


// 收藏/取消  
router.post('/collect', async (req, res) => {
    const { user_id, recipe_id } = req.body;
    if (!user_id || !recipe_id) {
        return res.status(400).json({ code: 400, message: "参数缺失" });
    }
    if (!user_id) {
        return res.status(400).json({ code: 400, message: "user_id必填" });
    }


    const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
    if (!rowsx || rowsx.length === 0) {
        return res.status(404).json({ code: 404, message: "用户不存在" });
    }
    try {
        const rows = await mysqlQuery(
            'SELECT id FROM Recipe_Collect WHERE user_id = ? AND recipe_id = ?',
            [user_id, recipe_id]
        );
        if (rows.length > 0) {
            await mysqlQuery(
                'DELETE FROM Recipe_Collect WHERE user_id = ? AND recipe_id = ?',
                [user_id, recipe_id]
            );
        } else {
            const id = utils.createId();
            await mysqlQuery(
                'INSERT INTO Recipe_Collect (id, user_id, recipe_id) VALUES (?, ?, ?)',
                [id, user_id, recipe_id]
            );
        }
        res.send({
            code: 0,
            message: 'success'
        });
    } catch (e) {
        res.status(500).json({ code: 500, message: "服务器异常" });
    }
});
//获取收藏列表
router.post('/collect/list', async (req, res) => {
    const { user_id, page = 1, size = 10 } = req.body;
    if (!user_id) {
        return res.status(400).json({ code: 400, msg: "参数缺失" });
    }
    const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
    if (!rowsx || rowsx.length === 0) {
        return res.status(404).json({ code: 404, message: "用户不存在" });
    }
    try {
        const pageNum = Math.max(Number(page), 1);
        const pageSize = Math.max(Number(size), 1);
        const offset = (pageNum - 1) * pageSize;

        // 1. 获取收藏总数
        const totalRows = await mysqlQuery(
            'SELECT COUNT(*) as cnt FROM Recipe_Collect WHERE user_id = ?',
            [user_id]
        );
        const total = totalRows[0].cnt;

        // 2. 子查询分页取收藏id和菜谱id
        const collects = await mysqlQuery(
            `SELECT id as collect_id, recipe_id, created_at as collected_at
             FROM Recipe_Collect
             WHERE user_id = ?
             ORDER BY created_at DESC
             LIMIT ? OFFSET ?`,
            [user_id, pageSize, offset]
        );
        if (!collects.length) {
            return res.send({
                code: 0,
                msg: 'success',
                data: { total, page: pageNum, size: pageSize, list: [] }
            });
        }

        // 3. 批量并发查Recipes表详情
        const recipeIds = collects.map(row => row.recipe_id);
        // 保证不会因为分页跨页导致顺序错乱
        const recipesRows = await mysqlQuery(
            `SELECT *
             FROM Recipes
             WHERE id IN (${recipeIds.map(() => '?').join(',')})`,
            recipeIds
        );
        // 建立id->菜谱对象映射
        const recipeMap = {};
        for (const row of recipesRows) recipeMap[row.id] = row;

        const resultList = collects.map(item => {
            const row = recipeMap[item.recipe_id] || {};
            let cuisine = [], dietary_preference = [], ingredients = [], steps = [], allergies = [];
            try { cuisine = JSON.parse(row.cuisine || '[]'); } catch { }
            try { dietary_preference = JSON.parse(row.dietary_preference || '[]'); } catch { }
            try { ingredients = JSON.parse(row.ingredients || '[]'); } catch { }
            try { allergies = JSON.parse(row.allergies || '[]'); } catch { }
            try { steps = JSON.parse(row.steps || '[]'); } catch { }
            return {
                collect_id: item.collect_id,
                collected_at: item.collected_at,
                id: item.recipe_id,
                english_name: row.english_name || '',
                translated_name: row.translated_name || '',
                flavor: row.flavor || '',
                cuisine,
                dietary_preference,
                meal_time: row.meal_time ? row.meal_time.split(',') : [],
                categories: row.categories ? row.categories.split(',') : [],
                cooking_effort: row.cooking_effort ? row.cooking_effort.split(',') : [],
                ingredients,
                allergies,
                total_mins: row.total_mins || 0,
                min_servings: row.min_servings || 0,
                max_servings: row.max_servings || 0,
                prep_mins: row.prep_mins || 0,
                cook_mins: row.cook_mins || 0,
                dish_presentation_description: row.dish_presentation_description ? row.dish_presentation_description.split(',') : [],
                protein_g: row.protein_g,
                fat_g: row.fat_g,
                carbs_g: row.carbs_g,
                calories: row.calories || 0,
                steps,
                matched_ingredients: row.matched_ingredients ? row.matched_ingredients.split(',') : [],
                cuisine_match: row.cuisine_match || 0,
                diet_match: row.diet_match || 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
                img_URL: row.img_URL
            };
        });

        res.send({
            code: 0,
            msg: 'success',
            data: {
                total,
                page: pageNum,
                size: pageSize,
                list: resultList
            }
        });
    } catch (e) {
        console.error('[collect/list] 获取收藏列表出错:', e);
        res.status(500).json({ code: 500, msg: "服务器异常" });
    }
});


// 删除自建菜谱关系
router.post('/userRecipe/remove', async (req, res) => {
    const { user_id, recipe_id } = req.body;
    if (!user_id || !recipe_id) {
        return res.status(400).json({ code: 400, message: "参数缺失" });
    }
    const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
    if (!rowsx || rowsx.length === 0) {
        return res.status(404).json({ code: 404, message: "用户不存在" });
    }
    try {
        const result = await mysqlQuery(
            'DELETE FROM Recipe_UserRecipe WHERE user_id = ? AND recipe_id = ?',
            [user_id, recipe_id]
        );
        if (result.affectedRows > 0) {
            res.send({
                code: 0,
                message: 'success'
            });
        } else {
            res.send({
                code: 0,
                message: '未找到匹配记录，无需删除'
            });
        }
    } catch (e) {
        res.status(500).json({ code: 500, message: "服务器异常" });
    }
});

//获取用户菜谱列表
router.post('/userRecipe/list', async (req, res) => {
    const { user_id, page = 1, size = 10 } = req.body;
    if (!user_id) {
        return res.status(400).json({ code: 400, msg: "参数缺失" });
    }
    const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
    if (!rowsx || rowsx.length === 0) {
        return res.status(404).json({ code: 404, message: "用户不存在" });
    }
    try {
        const pageNum = Math.max(Number(page), 1);
        const pageSize = Math.max(Number(size), 1);
        const offset = (pageNum - 1) * pageSize;

        // 1. 获取总数
        const totalRows = await mysqlQuery(
            'SELECT COUNT(*) as cnt FROM Recipe_UserRecipe WHERE user_id = ?',
            [user_id]
        );
        const total = totalRows[0].cnt;

        // 2. 分页查询 Recipe_UserRecipe 拿到目标 recipe_id
        const userRecipes = await mysqlQuery(
            `SELECT id as user_recipe_id, recipe_id, created_at
             FROM Recipe_UserRecipe
             WHERE user_id = ?
             ORDER BY created_at DESC
             LIMIT ? OFFSET ?`,
            [user_id, pageSize, offset]
        );
        if (!userRecipes.length) {
            return res.send({
                code: 0,
                msg: 'success',
                data: { total, page: pageNum, size: pageSize, list: [] }
            });
        }

        // 3. 一次性批量拿 Recipes 详情
        const recipeIds = userRecipes.map(row => row.recipe_id);
        const recipesRows = await mysqlQuery(
            `SELECT * FROM Recipes
             WHERE id IN (${recipeIds.map(() => '?').join(',')})`,
            recipeIds
        );
        const recipeMap = {};
        for (const row of recipesRows) recipeMap[row.id] = row;
        // ===== 新增 查收藏 =====
        const collectedIds = await getCollectedRecipeIds(user_id, recipeIds);
        const resultList = userRecipes.map(item => {
            const row = recipeMap[item.recipe_id] || {};
            let cuisine = [], dietary_preference = [], ingredients = [], steps = [], allergies = [];
            try { cuisine = JSON.parse(row.cuisine || '[]'); } catch { }
            try { dietary_preference = JSON.parse(row.dietary_preference || '[]'); } catch { }
            try { ingredients = JSON.parse(row.ingredients || '[]'); } catch { }
            try { allergies = JSON.parse(row.allergies || '[]'); } catch { }
            try { steps = JSON.parse(row.steps || '[]'); } catch { }
            return {
                created_at: item.created_at,
                id: item.recipe_id,
                english_name: row.english_name || '',
                translated_name: row.translated_name || '',
                flavor: row.flavor || '',
                cuisine,
                dietary_preference,
                meal_time: row.meal_time ? row.meal_time.split(',') : [],
                categories: row.categories ? row.categories.split(',') : [],
                cooking_effort: row.cooking_effort ? row.cooking_effort.split(',') : [],
                ingredients,
                allergies,
                total_mins: row.total_mins || 0,
                min_servings: row.min_servings || 0,
                max_servings: row.max_servings || 0,
                prep_mins: row.prep_mins || 0,
                cook_mins: row.cook_mins || 0,
                dish_presentation_description: row.dish_presentation_description ? row.dish_presentation_description.split(',') : [],
                protein_g: row.protein_g,
                fat_g: row.fat_g,
                carbs_g: row.carbs_g,
                calories: row.calories || 0,
                steps,
                matched_ingredients: row.matched_ingredients ? row.matched_ingredients.split(',') : [],
                cuisine_match: row.cuisine_match || 0,
                diet_match: row.diet_match || 0,
                recipes_created_at: row.created_at, // 所属菜谱创建时间
                recipes_updated_at: row.updated_at,
                img_URL: row.img_URL,
                // === 新增字段 ===
                is_collect: collectedIds.includes(item.recipe_id) ? true : false
            };
        });

        res.send({
            code: 0,
            msg: 'success',
            data: {
                total,
                page: pageNum,
                size: pageSize,
                list: resultList
            }
        });
    } catch (e) {
        console.error('[userRecipe/list] 获取自建菜谱列表出错:', e);
        res.status(500).json({ code: 500, msg: "服务器异常" });
    }
});

//获取推荐历史记录
router.post('/dailyRecommend/list', async (req, res) => {
    const { user_id, recommend_date } = req.body;
    if (!user_id || !recommend_date) {
        return res.status(400).json({ code: 400, msg: "参数缺失" });
    }
    const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
    if (!rowsx || rowsx.length === 0) {
        return res.status(404).json({ code: 404, message: "用户不存在" });
    }
    try {
        // 1. 查询当日所有推荐的 recipe_id
        const dailyRows = await mysqlQuery(
            `SELECT recipe_id, created_at
             FROM Recipe_DailyRecommend
             WHERE user_id = ? AND recommend_date = ?`,
            [user_id, recommend_date]
        );
        if (!dailyRows.length) {
            return res.send({
                code: 0,
                msg: 'success',
                data: []
            });
        }
        // 2. 查询菜谱详情
        const recipeIds = dailyRows.map(row => row.recipe_id);
        const recipesRows = await mysqlQuery(
            `SELECT * FROM Recipes
             WHERE id IN (${recipeIds.map(() => '?').join(',')})`,
            recipeIds
        );
        const recipeMap = {};
        for (const row of recipesRows) recipeMap[row.id] = row;
        // ===== 新增：查收藏 =====
        const collectedIds = await getCollectedRecipeIds(user_id, recipeIds);

        // 3. 整理列表
        const resultList = dailyRows.map(item => {
            const row = recipeMap[item.recipe_id] || {};
            let cuisine = [], dietary_preference = [], ingredients = [], steps = [], allergies = [];
            try { cuisine = JSON.parse(row.cuisine || '[]'); } catch { }
            try { dietary_preference = JSON.parse(row.dietary_preference || '[]'); } catch { }
            try { ingredients = JSON.parse(row.ingredients || '[]'); } catch { }
            try { allergies = JSON.parse(row.allergies || '[]'); } catch { }
            try { steps = JSON.parse(row.steps || '[]'); } catch { }
            return {
                recommend_date,
                daily_recommend_created_at: item.created_at, // 推荐生成时间
                id: item.recipe_id,
                english_name: row.english_name || '',
                translated_name: row.translated_name || '',
                flavor: row.flavor || '',
                cuisine,
                dietary_preference,
                meal_time: row.meal_time ? row.meal_time.split(',') : [],
                categories: row.categories ? row.categories.split(',') : [],
                cooking_effort: row.cooking_effort ? row.cooking_effort.split(',') : [],
                ingredients,
                allergies,
                total_mins: row.total_mins || 0,
                min_servings: row.min_servings || 0,
                max_servings: row.max_servings || 0,
                prep_mins: row.prep_mins || 0,
                cook_mins: row.cook_mins || 0,
                dish_presentation_description: row.dish_presentation_description ? row.dish_presentation_description.split(',') : [],
                protein_g: row.protein_g,
                fat_g: row.fat_g,
                carbs_g: row.carbs_g,
                calories: row.calories || 0,
                steps,
                matched_ingredients: row.matched_ingredients ? row.matched_ingredients.split(',') : [],
                cuisine_match: row.cuisine_match || 0,
                diet_match: row.diet_match || 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
                img_URL: row.img_URL,
                // ===== 新增字段：is_collect，1表示已收藏，0表示未收藏 =====
                is_collect: collectedIds.includes(item.recipe_id) ? true : false
            };
        });

        res.send({
            code: 0,
            msg: 'success',
            data: resultList
        });
    } catch (e) {
        console.error('[dailyRecommend/list] 获取每日推荐列表出错:', e);
        res.status(500).json({ code: 500, msg: "服务器异常" });
    }
});

// 用户主动在客户端购买会员时调用（不限首次，也不限上次是否因取消掉过）
// router.post('/subscription/activateByClient', async (req, res) => {
//     const chinaTime = new Date();
//     const { user_id, body } = req.body;

//     if (!user_id || !body || typeof body !== 'object') {
//         return res.status(400).json({ code: 400, message: '参数错误：user_id 和 body 必须提供' });
//     }
//     const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
//     if (!rowsx || rowsx.length === 0) {
//         return res.status(404).json({ code: 404, message: "用户不存在" });
//     }
//     const {
//         productId,                // 商品ID
//         transactionId,            // 当前订单ID
//         originalTransactionId,    // 原始订单ID
//         purchaseDate,             // 当前订单购买时间（时间戳，毫秒）
//         subscriptionExpirationDate, // 订阅过期时间（时间戳，毫秒）
//         isTrialPeriod             // 是否试用
//     } = body;

//     try {
//         // 1. 查找用户信息
//         const userRes = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id = ?', [user_id]);
//         if (!userRes || userRes.length === 0) {
//             return res.status(404).json({ code: 404, message: '用户不存在' });
//         }
//         console.log(body, 'body-----' + chinaTime.toLocaleString());

//         // 2. 会员类型判定
//         let newUserType;
//         let now = Date.now();
//         const expires = Number(subscriptionExpirationDate || 0);

//         if (isTrialPeriod === true || isTrialPeriod === 'true') {
//             newUserType = expires > now ? 'trial_member' : 'regular';
//         } else {
//             newUserType = expires > now ? 'renewal_member' : 'regular';
//         }

//         // 3. 时间戳转北京时间字符串
//         function toBeijingStr(ts) {
//             if (!ts) return null;
//             return moment(Number(ts)).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
//         }
//         const subscription_start_date = toBeijingStr(purchaseDate);
//         const subscription_expiry_date = toBeijingStr(subscriptionExpirationDate);

//         // 4. 更新数据库，只更新和订阅有关的字段
//         const updateSQL = `
//             UPDATE Recipe_user SET
//                 user_type = ?,
//                 subscription_start_date = ?,
//                 subscription_expiry_date = ?,
//                 current_product_id = ?,
//                 transaction_id = ?,
//                 original_transaction_id = ?
//             WHERE user_id = ?
//         `;
//         const updateParams = [
//             newUserType,
//             subscription_start_date,
//             subscription_expiry_date,
//             productId || null,
//             transactionId || null,
//             originalTransactionId || null,
//             user_id
//         ];
//         await mysqlQuery(updateSQL, updateParams);

//         // 5. 查询并格式化最新用户数据
//         const users = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id = ?', [user_id]);
//         const updatedUser = users[0];
//         [
//             "subscription_start_date", "subscription_expiry_date",
//             "registration_time", "last_login_time", "invitation_time", "created_at", "updated_at"
//         ].forEach(field => {
//             if (updatedUser[field]) {
//                 updatedUser[field] = moment(updatedUser[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
//             }
//         });

//         return res.json({ code: 0, msg: '会员激活/购买成功', data: updatedUser });

//     } catch (e) {
//         console.error('activateByClient error:', e);
//         res.status(500).json({ code: 500, message: '服务器错误' });
//     }
// });
router.post('/subscription/activateByClient', async (req, res) => {
    const chinaTime = new Date();
    const { user_id, body } = req.body;
    const moment = require('moment-timezone');

    if (!user_id || !body || typeof body !== 'object') {
        return res.status(400).json({ code: 400, message: '参数错误：user_id 和 body 必须提供' });
    }

    // 1. 检查当前用户是否存在
    const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
    if (!rowsx || rowsx.length === 0) {
        return res.status(404).json({ code: 404, message: "用户不存在" });
    }

    const {
        productId,                // 商品ID
        transactionId,            // 当前订单ID
        originalTransactionId,    // 原始订单ID
        purchaseDate,             // 当前订单购买时间（时间戳，毫秒）
        subscriptionExpirationDate, // 订阅过期时间（时间戳，毫秒）
        isTrialPeriod             // 是否试用
    } = body;

    // --- 新增逻辑，开始 ---
    try {
        // a. 检查数据库是否有其他用户占用此 originalTransactionId
        const usersWithThisOTID = await mysqlQuery(
            `SELECT user_id FROM Recipe_user WHERE original_transaction_id = ? AND user_id != ?`,
            [originalTransactionId, user_id]
        );
        if (usersWithThisOTID && usersWithThisOTID.length > 0) {
            // 有，则批量更新这些账号为普通用户
            await mysqlQuery(
                `UPDATE Recipe_user SET 
                    user_type = 'regular',
                    subscription_start_date = NULL, 
                    subscription_expiry_date = NULL,
                    current_product_id = NULL,
                    transaction_id = NULL,
                    original_transaction_id = NULL
                WHERE original_transaction_id = ? AND user_id != ?`,
                [originalTransactionId, user_id]
            );
        }
        // --- 新增逻辑，结束 ---

        // b. 继续正常会员激活/续费逻辑
        let newUserType;
        let now = Date.now();
        const expires = Number(subscriptionExpirationDate || 0);

        if (isTrialPeriod === true || isTrialPeriod === 'true') {
            newUserType = expires > now ? 'trial_member' : 'regular';
        } else {
            newUserType = expires > now ? 'renewal_member' : 'regular';
        }

        // c. 时间戳转北京时间字符串辅助函数
        function toBeijingStr(ts) {
            if (!ts) return null;
            return moment(Number(ts)).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        }
        const subscription_start_date = toBeijingStr(purchaseDate);
        const subscription_expiry_date = toBeijingStr(subscriptionExpirationDate);

        // d. 更新目标用户的会员相关信息（会员身份、订单信息、订阅起止时间等）
        const updateSQL = `
            UPDATE Recipe_user SET
                user_type = ?,
                subscription_start_date = ?,
                subscription_expiry_date = ?,
                current_product_id = ?,
                transaction_id = ?,
                original_transaction_id = ?
            WHERE user_id = ?
        `;
        const updateParams = [
            newUserType,
            subscription_start_date,
            subscription_expiry_date,
            productId || null,
            transactionId || null,
            originalTransactionId || null,
            user_id
        ];
        await mysqlQuery(updateSQL, updateParams);

        // e. 查询并格式化最新用户数据
        const users = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id = ?', [user_id]);
        const updatedUser = users[0];
        [
            "subscription_start_date", "subscription_expiry_date",
            "registration_time", "last_login_time", "invitation_time", "created_at", "updated_at"
        ].forEach(field => {
            if (updatedUser[field]) {
                updatedUser[field] = moment(updatedUser[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            }
        });

        return res.json({ code: 0, msg: '会员激活/购买成功', data: updatedUser });

    } catch (e) {
        console.error('activateByClient error:', e);
        res.status(500).json({ code: 500, message: '服务器错误' });
    }
});


//ImgFoodParse
router.post("/getImgFoodParseConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Recipe_ImgConf WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateImgFoodParseConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_ImgConf
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                top_k_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateImgFoodParseConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_ImgConf
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                top_k = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getImgFoodParseVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Recipe_ImgConf`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addImgFoodParseVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Recipe_ImgConf WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Recipe_ImgConf ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Recipe_ImgConf SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})
async function imageUrlToBase64(imageUrl) {
    // 1. 以二进制形式拉取图片
    const response = await axios.get(imageUrl, { responseType: 'arraybuffer' });
    // 2. Buffer转base64
    return Buffer.from(response.data, 'binary').toString('base64');
}
router.post("/ai-img-food-parse", async (req, res) => {
    let imageArr = [];
    let _imagesForDel = [];
    try {
        // 解密参数
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') {
            // 提前删掉图片
            if (content.images) {
                _imagesForDel = content.images.split('|').map(i => i.trim()).filter(Boolean);
                _imagesForDel.forEach(img => safeDeleteImage(img));
            }
            return res.send({ code: 1, msg: 'code错误' });
        }

        // 请求参数
        let { language, version, images, user_id } = content;
        // 1. 这里将 images 按 | 切割成数组，并去掉多余的空格和空字符串
        const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
        if (!rowsx || rowsx.length === 0) {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }

        // 获取图片 url 数组
        if (images) {
            imageArr = images.split('|').map(i => i.trim()).filter(i => !!i);
            _imagesForDel = imageArr.concat(); // 记录所有
        }
        // 查AI参数
        let sql = `SELECT * FROM Recipe_ImgConf WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Recipe_ImgConf ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, top_k, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';


        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });
        // == 这里循环 imageArr ，每个都生成 message ==
        if (imageArr.length > 0) {
            for (let url of imageArr) {
                if (url && url.trim() !== "") {
                    messages.push({
                        role: "user",
                        content: [
                            {
                                type: "image_url",
                                image_url: {
                                    url: url
                                }
                            }
                        ]
                    });
                }
            }
        }


        if (language && language.trim()) {
            messages.push({
                role: "user",
                content: [
                    { type: "text", text: `"output_language"="${language}"` }
                ]
            });
        }

        // 发 openai
        const response = await axios.post(
            'https://api-st.siliconflow.cn/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                top_k: Number(top_k)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer sk-jwxwusgxhligjvuqchrvjhthmwlnepwwnjyywhtynppwvdpl`
                }
            }
        );


        let message = JSON.stringify(response.data.choices[0].message);


        // 加密返回值（如需加密，按你的 encrypt 定义，否则用 message 或原始 choices 数据）
        const encrypted = encrypt(message, config.cryptoKey, config.cryptoIv);
        // 返回前无论成功or失败都做图片清理
        _imagesForDel.forEach(img => safeDeleteImage(img));
        res.send({
            code: 0,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        if (_imagesForDel.length) {
            _imagesForDel.forEach(img => safeDeleteImage(img));
        }
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
router.post("/getImgFoodParseApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_food_scan_api_logs`;
    let totalBase = "FROM Calorie_food_scan_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelImgFoodParse", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_food_scan_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelImgFoodParse", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_food_scan_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});


//FoodGen
router.post("/getFoodGenConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Recipe_FoodGenConf WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFoodGenConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_FoodGenConf
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                top_k_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateFoodGenConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_FoodGenConf
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                top_k = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getFoodGenVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Recipe_FoodGenConf`;
        let result = await mysqlQuery(sql, []);
        console.log(result)
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addFoodGenVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Recipe_FoodGenConf WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Recipe_FoodGenConf ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Recipe_FoodGenConf SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/ai-food-gen", async (req, res) => {
    try {
        // 解密参数
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });

        // 请求参数
        let { user_id, language, ingredients, version } = content;
        console.log('[AI-FOOD-GEN] 请求参数:', { user_id, language, ingredients, version });

        // 2. 查询用户表，获取三项信息
        let userSql = `SELECT dietary_preference, cuisine, cuisine_dislike, cuisine_left FROM Recipe_user WHERE user_id = ?`;
        let userResult = await mysqlQuery(userSql, [user_id]);
        let dietary_preference = '', cuisine = '', cuisine_dislike = '', cuisine_left = '';
        if (userResult && userResult.length > 0) {
            dietary_preference = userResult[0].dietary_preference || '';
            cuisine = userResult[0].cuisine || '';
            cuisine_dislike = userResult[0].cuisine_dislike || '';
            cuisine_left = userResult[0].cuisine_left || '';
        } else {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }

        console.log('[AI-FOOD-GEN] 用户偏好:', {
            dietary_preference,
            cuisine,
            cuisine_dislike,
            cuisine_left
        });

        // 预处理参数，确保每个都是数组格式
        // --- 参数格式统一处理 ---
        let ingredientsArray;
        if (Array.isArray(ingredients)) {
            ingredientsArray = ingredients
                .join(',')                               // 合并所有为一个大字符串
                .replace(/，/g, ',')                     // 中文逗号转英文逗号
                .split(',')
                .map(s => s.trim())
                .filter(Boolean);
        } else if (typeof ingredients === 'string') {
            ingredientsArray = ingredients
                .replace(/，/g, ',')
                .split(',')
                .map(s => s.trim())
                .filter(Boolean);
        } else {
            ingredientsArray = [];
        }

        console.log('ingredientsArray================', ingredientsArray);
        const cuisineArray = cuisine ? cuisine.split(',').map(item => item.trim()) : [];
        console.log('cuisineArray================', cuisineArray);

        const cuisineDislikeArray = cuisine_dislike ? cuisine_dislike.split(',').map(item => item.trim()) : [];
        console.log('cuisineDislikeArray================', cuisineDislikeArray);

        const cuisineLeftArray = cuisine_left ? cuisine_left.split(',').map(item => item.trim()) : [];
        console.log('cuisineLeftArray================', cuisineLeftArray);

        const dietaryPreferenceArray = dietary_preference ? dietary_preference.split(',').map(item => item.trim()) : [];
        console.log('dietaryPreferenceArray================', dietaryPreferenceArray);

        console.log('[AI-FOOD-GEN] 处理后的参数:', {
            ingredientsArray,
            cuisineArray,
            cuisineDislikeArray,
            cuisineLeftArray,
            dietaryPreferenceArray
        });

        // 新增功能：先从数据库查找匹配的菜谱
        const matchingRecipes = await findMatchingRecipes(
            user_id,
            ingredientsArray,
            cuisineArray,
            cuisineDislikeArray,
            cuisineLeftArray,
            dietaryPreferenceArray
        );

        console.log(`[AI-FOOD-GEN] 匹配到 ${matchingRecipes.length} 个菜谱`);

        // 如果找到匹配的菜谱，直接返回这些菜谱（最多3个）
        if (matchingRecipes.length > 0) {
            console.log('[AI-FOOD-GEN] 使用数据库匹配菜谱:', matchingRecipes.map(r => r.id));

            // 为每个匹配的菜谱添加到用户的"我的菜谱"
            for (const recipe of matchingRecipes) {
                const userRecipeId = utils.createId();
                await mysqlQuery(
                    'INSERT IGNORE INTO Recipe_UserRecipe (id, user_id, recipe_id) VALUES (?, ?, ?)',
                    [userRecipeId, user_id, recipe.id]
                );
            }
            const encrypteds = encrypt(JSON.stringify(matchingRecipes), config.cryptoKey, config.cryptoIv);

            return res.send({
                code: 0,
                msg: 'success',
                data: encrypteds
            });
        } else {
            console.log('[AI-FOOD-GEN] 未匹配到菜谱，调用AI接口生成');
        }

        // 如果没有找到匹配的菜谱，调用AI接口生成
        // 查AI参数
        let sql = `SELECT * FROM Recipe_FoodGenConf WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Recipe_FoodGenConf ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, top_k, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';

        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });

        messages.push({
            role: "user",
            content: [
                {
                    type: "text",
                    text: `ingredients:${ingredients},output_language:${language},cuisine:${cuisine},cuisine_dislike:${cuisine_dislike},dietary_preference:${dietary_preference},`
                }
            ]
        });

        // 发 openai
        const response = await axios.post(
            'https://api-st.siliconflow.cn/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                top_k: Number(top_k)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer sk-jwxwusgxhligjvuqchrvjhthmwlnepwwnjyywhtynppwvdpl`
                }
            }
        );


        let message = JSON.stringify(response.data.choices[0].message);


        const messageData = JSON.parse(message);
        console.log('messageData----------------------------', messageData)
        const parsedData = extractAndParseJson(messageData.content);
        console.log('parsedData-------------', parsedData)

        const recipeList = parsedData.recipeRecommendations.recommendedRecipes;
        // 新增：用于结果返回
        const insertedRecipes = [];

        for (const recipe of recipeList) {
            if (!isValidRecipe(recipe)) {
                console.warn('[自动菜谱入库] 校验未通过，跳过:', recipe);
                continue;
            }
            console.log('recipe--------------------------------', recipe);
            const metadata = recipe.metadata || {};
            const details = recipe.details || {};
            const compliance = recipe.compliance || {};

            // --- 原有数据整理 ---
            const ingredientsArr = details.ingredients || [];
            const ingredientsJson = JSON.stringify(ingredientsArr);

            const allergiesSet = new Set();
            for (const ing of ingredientsArr) {
                if (Array.isArray(ing.allergies)) {
                    ing.allergies.forEach(a => allergiesSet.add(a));
                }
            }
            const allergiesJson = JSON.stringify(Array.from(allergiesSet));

            const cuisineJson = JSON.stringify(metadata.cuisine || []);
            const dietaryPreferenceJson = JSON.stringify(metadata.dietary_preference || []);
            const mealTime = Array.isArray(metadata.meal_time) ? metadata.meal_time.join(',') : (metadata.meal_time || '');
            const categories = Array.isArray(metadata.Categories) ? metadata.Categories.join(',') : (metadata.Categories || '');
            const timeObj = metadata.time || {};
            const servings = metadata.Servings || {};
            const stepsJson = details.steps ? JSON.stringify(details.steps) : null;

            // --- 入库 ---
            const id = utils.createId();
            const matchedIngredients = compliance.matchedIngredients ? compliance.matchedIngredients.join(',') : '';

            const insertSql = `
            INSERT INTO Recipes (
              id,
              english_name, translated_name, flavor, cuisine, dietary_preference, meal_time,
              categories, cooking_effort, ingredients, allergies,
              total_mins, min_servings, max_servings, prep_mins, cook_mins,
              dish_presentation_description,
              protein_g, fat_g, carbs_g, calories,
              steps, matched_ingredients, cuisine_match, diet_match
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE updated_at = NOW()
                      `;
            const values = [
                id,
                metadata.english_name || '',
                metadata.translated_name || '',
                metadata.flavor || '',
                cuisineJson,
                dietaryPreferenceJson,
                mealTime,
                categories,
                metadata.cooking_effort || '',
                ingredientsJson,
                allergiesJson,
                timeObj.total_mins || null,
                servings.min_servings || null,
                servings.max_servings || null,
                timeObj.prep_mins || null,
                timeObj.cook_mins || null,
                metadata.dish_presentation_description || '',
                (metadata.nutrition && metadata.nutrition.protein_g) || null,
                (metadata.nutrition && metadata.nutrition.fat_g) || null,
                (metadata.nutrition && metadata.nutrition.carbs_g) || null,
                (metadata.nutrition && metadata.nutrition.calories) || null,
                stepsJson,
                matchedIngredients,
                compliance.cuisineMatch ? 1 : 0,
                compliance.dietMatch ? 1 : 0,
            ];

            try {
                await mysqlQuery(insertSql, values);
                // 自动把新生成的菜谱添加到用户"我的菜谱"
                const userRecipeId = utils.createId();
                await mysqlQuery(
                    'INSERT IGNORE INTO Recipe_UserRecipe (id, user_id, recipe_id) VALUES (?, ?, ?)',
                    [userRecipeId, user_id, id]
                );

                // 构造带 id、关键信息的返回对象，根据你业务需要定制
                const returnRecipe = {
                    id,  // 数据库主键
                    english_name: metadata.english_name || '',
                    translated_name: metadata.translated_name || '',
                    flavor: Array.isArray(metadata.flavor)
                        ? metadata.flavor.join(', ')
                        : (metadata.flavor || '') || '',
                    cuisine: metadata.cuisine || [],
                    dietary_preference: metadata.dietary_preference || [],
                    // meal_time: metadata.meal_time || [],
                    categories: metadata.Categories || [],
                    // cooking_effort: metadata.cooking_effort || [],
                    cooking_effort: Array.isArray(metadata.cooking_effort)
                        ? metadata.cooking_effort
                        : (typeof metadata.cooking_effort === 'string'
                            ? metadata.cooking_effort.split(',')
                            : []
                        ),
                    meal_time: Array.isArray(metadata.meal_time)
                        ? metadata.meal_time
                        : (typeof metadata.meal_time === 'string'
                            ? metadata.meal_time.split(',')
                            : []
                        ),
                    // servings: metadata.Servings || {},
                    // time: metadata.time || {},
                    total_mins: timeObj.total_mins || 0,
                    prep_mins: timeObj.prep_mins || 0,
                    cook_mins: timeObj.cook_mins || 0,
                    min_servings: servings.min_servings || 0,
                    max_servings: servings.max_servings || 0,
                    protein_g: metadata.nutrition.protein_g,
                    fat_g: metadata.nutrition.fat_g,
                    carbs_g: metadata.nutrition.carbs_g,
                    calories: metadata.nutrition.calories || 0,
                    // dish_presentation_description: metadata.dish_presentation_description || [],
                    dish_presentation_description: Array.isArray(metadata.dish_presentation_description)
                        ? metadata.dish_presentation_description
                        : (typeof metadata.dish_presentation_description === 'string'
                            ? metadata.dish_presentation_description.split(',')
                            : []
                        ),
                    steps: details.steps || [],
                    ingredients: details.ingredients || [],
                    matched_ingredients: compliance.matchedIngredients || [],
                    cuisine_match: compliance.cuisineMatch ? 1 : 0,
                    diet_match: compliance.dietMatch ? 1 : 0,
                    img_URL: null
                };
                insertedRecipes.push(returnRecipe);
                // console.log(returnRecipe)
            } catch (err) {
                console.error('插入菜谱失败', err, recipe);
            }
        }

        let code = 0;
        if (insertedRecipes.length == 0) {
            code = 1
        }
        const encrypted = encrypt(JSON.stringify(insertedRecipes), config.cryptoKey, config.cryptoIv);

        res.send({
            code: code,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
function isValidRecipe(recipe) {
    // 基本结构解包
    const metadata = recipe.metadata || {};
    const details = recipe.details || {};
    // 校验关键字段，不合格直接跳过
    if (!Array.isArray(details.ingredients) || details.ingredients.length === 0) return false;
    if (!details.steps || !details.steps.length) return false;
    return true;
}
function extractAndParseJson(content) {
    try {
        // 1. 优先尝试提取常见markdown代码块（支持多种语言标签）
        // 兼容```json、```js、```javascript、```ts等。也兼容无语言标签。
        // 支持首尾有空行、或开头有三重/单引号的情形。
        let blockMatch = content.match(/```(?:[\w\W]*?)(\{[\s\S]*\})\s*```/m);
        let jsonStr = blockMatch ? blockMatch[1] : content;

        // 2. 清理特殊情况
        jsonStr = jsonStr
            .replace(/^\uFEFF/, '') // 去掉 BOM
            .replace(/^[ \t\r\n'"`]+/, '') // 去掉开头多余字符
            .replace(/[ \t\r\n'"`]+$/, '') // 去掉结尾多余字符
            .replace(/，/g, ',') // 全角逗号转半角
            .replace(/(\},)\s*([\]}])/g, '$1$2'); // 尾部多余逗号基本 fix

        // 3. 强容错 - 尝试用 Function 构造（能解析JSON对象字面量/数组/数字/字符串）
        try {
            return JSON.parse(jsonStr);
        } catch (e2) {
            // 针对不是纯JSON的情况
            // 如果是对象/数组字面量可用Function解析
            if (/^\s*[\[{]/.test(jsonStr)) {
                // eslint-disable-next-line no-new-func
                return (new Function('return ' + jsonStr))();
            } else {
                throw e2;
            }
        }
    } catch (error) {
        console.error('JSON解析失败1:', error.message);
        console.log('原始内容1:', content);
        return null;
    }
}

/**
 * 根据条件从数据库中查找匹配的菜谱
 * @param {string} userId - 用户ID
 * @param {Array<string>} ingredients - 食材列表
 * @param {Array<string>} cuisines - 喜欢的菜系
 * @param {Array<string>} cuisinesDislike - 不喜欢的菜系
 * @param {Array<string>} cuisinesLeft - 剩余可选菜系
 * @param {Array<string>} dietaryPreferences - 饮食偏好
 * @returns {Promise<Array>} - 匹配的菜谱列表，最多3个
 */

// 假设有 mysqlQuery、mysql.escape 方法 —— 你的业务环境中已具备

async function findMatchingRecipes(userId, ingredients, cuisines, cuisinesDislike, cuisinesLeft, dietaryPreferences) {
    console.log('[findMatchingRecipes] 开始查找匹配菜谱，参数:', {
        userId,
        ingredients,
        cuisines,
        cuisinesDislike,
        cuisinesLeft,
        dietaryPreferences
    });

    try {
        // 查询用户已有的菜谱ID，用于后续排除
        const userRecipesQuery = `
            SELECT recipe_id FROM Recipe_UserRecipe WHERE user_id = ?
            UNION
            SELECT recipe_id FROM Recipe_Collect WHERE user_id = ?
            UNION
            SELECT recipe_id FROM Recipe_DailyRecommend WHERE user_id = ?
        `;
        const userRecipesResult = await mysqlQuery(userRecipesQuery, [userId, userId, userId]);
        const userRecipeIds = userRecipesResult.map(row => row.recipe_id);
        console.log(`[findMatchingRecipes] 用户已有的菜谱ID: ${userRecipeIds.length}个`);

        // SQL条件拼接
        let whereArr = [];
        let sqlParams = [];
        if (userRecipeIds.length > 0) {
            whereArr.push(`id NOT IN (${userRecipeIds.map(() => '?').join(',')})`);
            sqlParams.push(...userRecipeIds);
        } else {
            whereArr.push('1=1');
        }

        // 过敏源（不可有）
        if (dietaryPreferences && dietaryPreferences.length) {
            whereArr.push(
                dietaryPreferences
                    .map(a => `(NOT JSON_CONTAINS(allergies, ${mysql.escape(`"${a}"`)}) OR allergies IS NULL)`)
                    .join(' AND ')
            );
        }

        // 喜欢菜系
        let cuisinesAll = [];
        if (Array.isArray(cuisines)) cuisinesAll = cuisinesAll.concat(cuisines);
        if (Array.isArray(cuisinesLeft)) cuisinesAll = cuisinesAll.concat(cuisinesLeft);
        cuisinesAll = cuisinesAll.filter(Boolean);
        //ingredientMatchCount 
        if (cuisinesAll.length) {
            whereArr.push(
                '(' + cuisinesAll
                    .map(c => `JSON_SEARCH(cuisine, 'one', ${mysql.escape('%' + c + '%')}) IS NOT NULL`)
                    .join(' OR ') + ')'
            );
        }

        // 不喜欢菜系
        if (cuisinesDislike && cuisinesDislike.length) {
            whereArr.push(
                cuisinesDislike
                    .map(c => `(JSON_SEARCH(cuisine, 'one', ${mysql.escape('%' + c + '%')}) IS NULL OR cuisine IS NULL)`)
                    .join(' AND ')
            );
        }

        // 【dietary_preference 不在SQL层过滤】

        const filterSql = `
            SELECT *
            FROM Recipes
            WHERE ${whereArr.join('\n  AND ')}
            LIMIT 20000
        `;
        console.log('[findMatchingRecipes] SQL:', filterSql);
        const prefilterRecipes = await mysqlQuery(filterSql, sqlParams);
        console.log(`[findMatchingRecipes] 预筛选出 ${prefilterRecipes.length} 个可能的菜谱`);

        // JS后处理&食材&dietary优先级
        let filteredCount = 0;
        const matchingRecipes = [];
        for (const recipe of prefilterRecipes) {
            try {
                // 只输出第一个样本，便于debug
                if (filteredCount === 0) {
                    console.log('[findMatchingRecipes] 菜谱样本:', {
                        id: recipe.id,
                        ingredients: recipe.ingredients ? recipe.ingredients.substring(0, 100) + '...' : null,
                        cuisine: recipe.cuisine,
                        allergies: recipe.allergies,
                        dietary_preference: recipe.dietary_preference
                    });
                }

                // ingredients处理
                const recipeIngredientsRaw = recipe.ingredients;
                let recipeIngredients = [];
                try {
                    recipeIngredients = JSON.parse(recipeIngredientsRaw || '[]');
                    if (filteredCount === 0) {
                        console.log('[findMatchingRecipes] 解析后的食材:', recipeIngredients.length > 0 ? recipeIngredients.slice(0, 2) : 'Empty array');
                    }
                } catch (e) {
                    console.error(`[findMatchingRecipes] 食材JSON解析失败 ${recipe.id}:`, e.message, recipeIngredientsRaw);
                }

                // 只做 ingredients 匹配 ≥2种
                // 新写法：以用户食材为主轴
                const userIngredientsMatched = []; // 记录被命中的用户食材名
                if (Array.isArray(recipeIngredients) && ingredients && ingredients.length) {
                    for (const userIngredient of ingredients) {
                        const matched = recipeIngredients.some(ingredient => {
                            let ingredientName = '';
                            if (typeof ingredient === 'string') {
                                ingredientName = ingredient;
                            } else if (ingredient && typeof ingredient === 'object') {
                                ingredientName = ingredient.name || ingredient.ingredient || '';
                            }
                            return ingredientName && ingredientName.toLowerCase().includes(userIngredient.toLowerCase());
                        });
                        if (matched) {
                            userIngredientsMatched.push(userIngredient);
                        }
                    }
                }
                if (userIngredientsMatched.length < 2) {
                    filteredCount++;
                    continue;
                }
                recipe.ingredient_match_count = userIngredientsMatched.length;
                recipe.matched_ingredients_names = userIngredientsMatched;

                // --- 新增 dietary_preference 命中数并记录 ---
                let dietaryMatchCount = 0;
                let recipeDiet = [];
                try {
                    recipeDiet = JSON.parse(recipe.dietary_preference || '[]');
                } catch (e) { }
                if (Array.isArray(dietaryPreferences) && dietaryPreferences.length && Array.isArray(recipeDiet)) {
                    dietaryMatchCount = dietaryPreferences
                        .filter(d => recipeDiet.some(recipeD =>
                            (typeof recipeD === 'string' && recipeD.toLowerCase().includes(d.toLowerCase()))
                            || (typeof recipeD === 'object' && recipeD.name && recipeD.name.toLowerCase().includes(d.toLowerCase()))
                        )).length;
                }
                recipe.dietary_match_count = dietaryMatchCount;

                matchingRecipes.push(recipe);
                filteredCount++;
            } catch (error) {
                filteredCount++;
            }
        }

        // 优先 ingredient_match_count 多的，再优先 dietary_match_count 多的
        matchingRecipes.sort((a, b) => {
            if (b.ingredient_match_count !== a.ingredient_match_count) {
                return b.ingredient_match_count - a.ingredient_match_count;
            }
            return b.dietary_match_count - a.dietary_match_count;
        });
        const limitedRecipes = matchingRecipes.slice(0, 3);

        // 输出
        return limitedRecipes.map(recipe => {
            let cuisine = [], dietaryPreference = [], ingredientsArr = [], allergies = [], steps = [];
            try { cuisine = JSON.parse(recipe.cuisine || '[]'); } catch (e) { }
            try { dietaryPreference = JSON.parse(recipe.dietary_preference || '[]'); } catch (e) { }
            try { ingredientsArr = JSON.parse(recipe.ingredients || '[]'); } catch (e) { }
            try { allergies = JSON.parse(recipe.allergies || '[]'); } catch (e) { }
            try { steps = JSON.parse(recipe.steps || '[]'); } catch (e) { }
            return {
                id: recipe.id,
                english_name: recipe.english_name,
                img_URL: recipe.img_URL,
                translated_name: recipe.translated_name,
                flavor: recipe.flavor,
                cuisine,
                dietary_preference: dietaryPreference,
                meal_time: recipe.meal_time ? recipe.meal_time.split(',') : [],
                categories: recipe.categories ? recipe.categories.split(',') : [],
                cooking_effort: recipe.cooking_effort ? recipe.cooking_effort.split(',') : [],
                min_servings: recipe.min_servings || 0,
                max_servings: recipe.max_servings || 0,
                total_mins: recipe.total_mins || 0,
                prep_mins: recipe.prep_mins || 0,
                cook_mins: recipe.cook_mins || 0,
                protein_g: recipe.protein_g,
                fat_g: recipe.fat_g,
                carbs_g: recipe.carbs_g,
                calories: recipe.calories || 0,
                dish_presentation_description: recipe.dish_presentation_description ? recipe.dish_presentation_description.split(',') : [],
                ingredients: ingredientsArr,
                steps: steps,
                matched_ingredients: recipe.matched_ingredients_names || [],
                cuisine_match: recipe.cuisine_match || 0,
                diet_match: recipe.diet_match || 0
            };
        });

    } catch (error) {
        console.error('[findMatchingRecipes] 查找匹配菜谱失败:', error);
        return [];
    }
}

/**
 * 根据条件从数据库中查找每日推荐匹配的菜谱
 * @param {string} userId - 用户ID
 * @param {Array<string>} allergies - 用户过敏原标签
 * @param {Array<string>} cuisines - 喜欢的菜系
 * @param {Array<string>} cuisinesDislike - 不喜欢的菜系
 * @param {Array<string>} dietaryPreferences - 饮食偏好
 * @returns {Promise<Array>} - 匹配的菜谱列表，最多3个
 */
async function findMatchingRecipesDaily(userId, allergies, cuisines, cuisinesDislike, dietaryPreferences) {
    console.log('[findMatchingRecipesDaily] 开始查找匹配菜谱，参数:', {
        userId, allergies, cuisines, cuisinesDislike, dietaryPreferences
    });

    try {
        // 1. 用户直接排除ID
        const userRecipesQuery = `
            SELECT recipe_id FROM Recipe_UserRecipe WHERE user_id = ?
            UNION
            SELECT recipe_id FROM Recipe_Collect WHERE user_id = ?
            UNION
            SELECT recipe_id FROM Recipe_DailyRecommend WHERE user_id = ?
        `;
        const userRecipesResult = await mysqlQuery(userRecipesQuery, [userId, userId, userId]);
        const userRecipeIds = userRecipesResult.map(row => row.recipe_id);
        console.log(`[findMatchingRecipesDaily] 用户已有的菜谱ID: ${userRecipeIds.length}个`);

        // 2. SQL标签拼接（只查极少数据）
        let whereArr = [];
        let sqlParams = [];

        if (userRecipeIds.length > 0) {
            whereArr.push(`id NOT IN (${userRecipeIds.map(() => '?').join(',')})`);
            sqlParams.push(...userRecipeIds);
        } else {
            whereArr.push('1=1');
        }

        // 过敏源不能有
        if (allergies && allergies.length) {
            whereArr.push(
                allergies
                    .map(a => `(NOT JSON_CONTAINS(allergies, ${mysql.escape(`"${a}"`)}) OR allergies IS NULL)`)
                    .join(' AND ')
            );
        }
        // 喜欢菜系，至少一个（模糊版）
        if (cuisines && cuisines.length) {
            whereArr.push(
                '(' + cuisines
                    .map(c => `JSON_SEARCH(cuisine, 'one', ${mysql.escape('%' + c + '%')}) IS NOT NULL`)
                    .join(' OR ') + ')'
            );
        }
        // 不喜欢菜系，不能有（模糊排除版）
        if (cuisinesDislike && cuisinesDislike.length) {
            whereArr.push(
                cuisinesDislike
                    .map(c => `(JSON_SEARCH(cuisine, 'one', ${mysql.escape('%' + c + '%')}) IS NULL OR cuisine IS NULL)`)
                    .join(' AND ')
            );
        }
        // 不再SQL层筛选 dietaryPreferences！

        // 拼SQL
        const filterSql = `
            SELECT *
            FROM Recipes
            WHERE ${whereArr.join('\n  AND ')}
            LIMIT 15000
        `;
        console.log('[findMatchingRecipesDaily] SQL:', filterSql);

        const candidateRecipes = await mysqlQuery(filterSql, sqlParams);
        console.log(`[findMatchingRecipesDaily] 预筛选出 ${candidateRecipes.length} 个候选菜谱`);

        // JS里做 dietary_match_count 排序
        const scoredRecipes = [];
        for (const recipe of candidateRecipes) {
            try {
                // 解析标签字段，不报错为 []
                let cuisine = []; let dietaryPreference = []; let ingredients = []; let allergiesR = []; let steps = [];
                try { cuisine = JSON.parse(recipe.cuisine || '[]'); } catch { }
                try { dietaryPreference = JSON.parse(recipe.dietary_preference || '[]'); } catch { }
                try { ingredients = JSON.parse(recipe.ingredients || '[]'); } catch { }
                try { allergiesR = JSON.parse(recipe.allergies || '[]'); } catch { }
                try { steps = JSON.parse(recipe.steps || '[]'); } catch { }

                // dietary_match_count 计算（同方案）
                let dietary_match_count = 0;
                if (Array.isArray(dietaryPreferences) && dietaryPreferences.length && Array.isArray(dietaryPreference)) {
                    dietary_match_count = dietaryPreferences
                        .filter(d => dietaryPreference.some(dp =>
                            (typeof dp === 'string' && dp.toLowerCase().includes(d.toLowerCase())) ||
                            (dp && typeof dp.name === 'string' && dp.name.toLowerCase().includes(d.toLowerCase()))
                        )).length;
                }

                scoredRecipes.push({
                    id: recipe.id,
                    english_name: recipe.english_name,
                    img_URL: recipe.img_URL,
                    translated_name: recipe.translated_name,
                    flavor: recipe.flavor,
                    cuisine,
                    dietary_preference: dietaryPreference,
                    meal_time: recipe.meal_time ? recipe.meal_time.split(',') : [],
                    categories: recipe.categories ? recipe.categories.split(',') : [],
                    cooking_effort: recipe.cooking_effort ? recipe.cooking_effort.split(',') : [],
                    min_servings: recipe.min_servings || 0,
                    max_servings: recipe.max_servings || 0,
                    total_mins: recipe.total_mins || 0,
                    prep_mins: recipe.prep_mins || 0,
                    cook_mins: recipe.cook_mins || 0,
                    protein_g: recipe.protein_g,
                    fat_g: recipe.fat_g,
                    carbs_g: recipe.carbs_g,
                    calories: recipe.calories || 0,
                    dish_presentation_description: recipe.dish_presentation_description ? recipe.dish_presentation_description.split(',') : [],
                    ingredients,
                    steps,
                    cuisine_match: recipe.cuisine_match || 0,
                    diet_match: recipe.diet_match || 0,
                    dietary_match_count, // 新增
                    matched_ingredients: recipe.matched_ingredients ? recipe.matched_ingredients.split(',') : [],

                });
            } catch (e) {
                // ignore
            }
        }
        // 随机洗牌算法
        function shuffle(array) {
            for (let i = array.length - 1; i > 0; i--) {
                const j = Math.floor(Math.random() * (i + 1));
                [array[i], array[j]] = [array[j], array[i]];
            }
        }

        // 1. 洗牌
        shuffle(scoredRecipes);

        // 2. 取前3个（去除dietary_match_count，不影响你的最终输出结构）
        const finalRecipes = scoredRecipes.slice(0, 3).map(({ dietary_match_count, ...rest }) => rest);
        // // dietary_match_count 降序、前3个
        // scoredRecipes.sort((a, b) => b.dietary_match_count - a.dietary_match_count);
        // const finalRecipes = scoredRecipes.slice(0, 3)
        //     .map(({ dietary_match_count, ...rest }) => rest); // 去掉 dietary_match_count 字段输出，如果你不想返回

        return finalRecipes;

    } catch (error) {
        console.error('[findMatchingRecipesDaily] 查找匹配菜谱失败:', error);
        return [];
    }
}


router.post("/getFoodGenApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_food_text_api_logs`;
    let totalBase = "FROM Calorie_food_text_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelFoodGen", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_food_text_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelFoodGen", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_food_text_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});



//ImgRecipe
router.post("/getImgRecipeConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Recipe_ImgRecipeConf WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateImgRecipeConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_ImgRecipeConf
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                top_k_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateImgRecipeConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_ImgRecipeConf
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                top_k = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getImgRecipeVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Recipe_ImgRecipeConf`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addImgRecipeVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Recipe_ImgRecipeConf WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Recipe_ImgRecipeConf ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Recipe_ImgRecipeConf SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/ai-img-Recipe", async (req, res) => {
    let imageValue = '';   // 记录image，出错也删
    try {
        // 解密参数
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });
        // 请求参数
        let { user_id, language, version, pk, image } = content;
        const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
        if (!rowsx || rowsx.length === 0) {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }
        imageValue = image || '';
        console.log('[AI-IMG-RECIPE] 请求参数:', { user_id, language, version, pk, image: image ? 'Image URL provided' : 'No image' });

        // 查AI参数
        let sql = `SELECT * FROM Recipe_ImgRecipeConf WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Recipe_ImgRecipeConf ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, top_k, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';

        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });

        if (image && image.trim() !== "") {
            messages.push({
                role: "user",
                content: [
                    {
                        type: "image_url",
                        image_url: {
                            url: image
                        }
                    }
                ]
            });
        }
        if (language && language.trim()) {
            messages.push({
                role: "user",
                content: [
                    { type: "text", text: `"output_language"="${language}"` }
                ]
            });
        }
        console.log(JSON.stringify(messages), 'messages---------------------------------------------------------------')
        // 发 openai
        const response = await axios.post(
            'https://api-st.siliconflow.cn/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                top_k: Number(top_k)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer sk-jwxwusgxhligjvuqchrvjhthmwlnepwwnjyywhtynppwvdpl`
                }
            }
        );

        let message = JSON.stringify(response.data.choices[0].message);
        const messageData = JSON.parse(message);
        console.log('[AI-IMG-RECIPE] 接收到AI响应');

        const parsedData = extractAndParseJson(messageData.content);
        console.log(parsedData)
        console.log('[AI-IMG-RECIPE] 解析数据结构完成');
        // 处理入库 - 主菜谱和相似菜谱
        const insertedRecipes = [];
        // 遍历分析结果
        for (const analysisResult of parsedData.analysisResults || []) {
            // 1. 保存主菜谱
            if (analysisResult.recipes) {

                const mainRecipe = analysisResult.recipes.identifiedRecipe;
                if (!isValidRecipe(mainRecipe)) {
                    console.warn('[AI-IMG-RECIPE] 主菜谱校验未通过，跳过:', mainRecipe);
                    continue; // 跳过该 analysisResult 的主菜谱
                }
                const mainRecipeId = await saveRecipe(mainRecipe, user_id);
                if (mainRecipeId) {

                    const returnRecipe = constructRecipeResponseObject(mainRecipe, mainRecipeId);
                    returnRecipe.is_main_recipe = true;
                    returnRecipe.img_URL = null;
                    insertedRecipes.push(returnRecipe);
                }
            }

            // 2. 保存相似菜谱
            if (Array.isArray(analysisResult.similarRecipes)) {


                for (const similarRecipe of analysisResult.similarRecipes) {
                    const similarRecipeWithDetails = {
                        metadata: similarRecipe.metadata || {},
                        details: similarRecipe.details || {}
                    };
                    if (!isValidRecipe(similarRecipeWithDetails)) {
                        console.warn('[AI-IMG-RECIPE] 相似菜谱校验未通过，跳过:', similarRecipeWithDetails);
                        continue;
                    }
                    const similarRecipeId = await saveRecipe(similarRecipeWithDetails, user_id);
                    if (similarRecipeId) {
                        const returnRecipe = constructRecipeResponseObject(similarRecipeWithDetails, similarRecipeId);
                        returnRecipe.is_main_recipe = false;
                        returnRecipe.img_URL = null;
                        // 添加相似度因素
                        if (similarRecipe.similarityFactors) {
                            returnRecipe.similarity_factors = {
                                shared_ingredients: similarRecipe.similarityFactors.sharedIngredients || [],
                                technique_match: similarRecipe.similarityFactors.techniqueMatch || [],
                                flavor_overlap: similarRecipe.similarityFactors.flavorOverlap || [],
                                similarity: similarRecipe.similarityFactors.similarity || 0
                            };
                        }

                        insertedRecipes.push(returnRecipe);
                    }
                }
            }
        }

        // 加密返回值
        const encrypted = encrypt(JSON.stringify(insertedRecipes), config.cryptoKey, config.cryptoIv);
        if (imageValue) safeDeleteImage(imageValue);
        res.send({
            code: 0,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        console.error('[AI-IMG-RECIPE] 错误:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
/**
 * 保存菜谱至数据库
 * @param {Object} recipe 菜谱数据
 * @param {string} user_id 用户ID
 * @returns {Promise<string|null>} 菜谱ID或null
 */
async function saveRecipe(recipe, user_id) {
    try {
        const metadata = recipe.metadata || {};
        const details = recipe.details || {};
        // --- 数据整理 ---
        const ingredientsArr = details.ingredients || [];
        const ingredientsJson = JSON.stringify(ingredientsArr);

        const allergiesSet = new Set();
        for (const ing of ingredientsArr) {
            if (Array.isArray(ing.allergies)) {
                ing.allergies.forEach(a => allergiesSet.add(a));
            }
        }
        const allergiesJson = JSON.stringify(Array.from(allergiesSet));

        const cuisineJson = JSON.stringify(metadata.cuisine || []);
        const dietaryPreferenceJson = JSON.stringify(metadata.dietary_preference || []);
        const mealTime = Array.isArray(metadata.meal_time) ? metadata.meal_time.join(',') : (metadata.meal_time || '');
        const categories = Array.isArray(metadata.Categories) ? metadata.Categories.join(',') : (metadata.Categories || '');
        const timeObj = metadata.time || {};
        const servings = metadata.Servings || {};
        const stepsJson = details.steps ? JSON.stringify(details.steps) : null;
        const dishPresentationDesc = Array.isArray(metadata.dish_presentation_description)
            ? metadata.dish_presentation_description.join('. ')
            : (metadata.dish_presentation_description || '');
        const flavor = Array.isArray(metadata.flavor)
            ? metadata.flavor.join(', ')
            : (metadata.flavor || '');

        // --- 生成ID并入库 ---
        const id = utils.createId();

        const insertSql = `
INSERT INTO Recipes (
    id,
    english_name, translated_name, flavor, cuisine, dietary_preference, meal_time,
    categories, cooking_effort, ingredients, allergies,
    total_mins, min_servings, max_servings, prep_mins, cook_mins,
    dish_presentation_description,
    protein_g, fat_g, carbs_g, calories,
    steps, matched_ingredients, cuisine_match, diet_match
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE updated_at = NOW()
`;

        const values = [
            id,
            metadata.english_name || '',
            metadata.translated_name || '',
            flavor,
            cuisineJson,
            dietaryPreferenceJson,
            mealTime,
            categories,
            Array.isArray(metadata.cooking_effort) ? metadata.cooking_effort.join(', ') : (metadata.cooking_effort || ''),
            ingredientsJson,
            allergiesJson,
            metadata?.time?.total_mins ?? null,
            metadata?.Servings?.min_servings ?? null,
            metadata?.Servings?.max_servings ?? null,
            metadata?.time?.prep_mins ?? null,
            metadata?.time?.cook_mins ?? null,
            dishPresentationDesc,
            metadata?.nutrition?.protein_g ?? null,
            metadata?.nutrition?.fat_g ?? null,
            metadata?.nutrition?.carbs_g ?? null,
            metadata?.nutrition?.calories ?? null,
            stepsJson,
            '',    // matched_ingredients
            0,     // cuisine_match
            0      // diet_match
        ];

        await mysqlQuery(insertSql, values);

        // 如果提供了用户ID，自动添加到"我的菜谱"
        if (user_id) {
            const userRecipeId = utils.createId();
            await mysqlQuery(
                'INSERT IGNORE INTO Recipe_UserRecipe (id, user_id, recipe_id) VALUES (?, ?, ?)',
                [userRecipeId, user_id, id]
            );
        }

        return id;
    } catch (err) {
        console.error('[AI-IMG-RECIPE] 插入菜谱失败', err);
        return null;
    }
}

/**
 * 构造菜谱返回对象
 * @param {Object} recipe 菜谱数据
 * @param {string} id 菜谱ID
 * @returns {Object} 返回对象
 */
function constructRecipeResponseObject(recipe, id) {
    const metadata = recipe.metadata || {};
    const details = recipe.details || {};

    return {
        id,  // 数据库主键
        english_name: metadata.english_name || '',
        translated_name: metadata.translated_name || '',
        flavor: Array.isArray(metadata.flavor)
            ? metadata.flavor.join(', ')
            : (metadata.flavor || ''),
        cuisine: metadata.cuisine ?? [],
        dietary_preference: metadata.dietary_preference ?? [],
        meal_time: Array.isArray(metadata.meal_time)
            ? metadata.meal_time
            : (metadata.meal_time ? [metadata.meal_time] : []),
        categories: Array.isArray(metadata.Categories)
            ? metadata.Categories
            : (metadata.Categories ? [metadata.Categories] : []),
        cooking_effort: Array.isArray(metadata.cooking_effort)
            ? metadata.cooking_effort
            : (metadata.cooking_effort ? [metadata.cooking_effort] : []),
        total_mins: metadata?.time?.total_mins ?? 0,
        prep_mins: metadata?.time?.prep_mins ?? 0,
        cook_mins: metadata?.time?.cook_mins ?? 0,
        min_servings: metadata?.Servings?.min_servings ?? 0,
        max_servings: metadata?.Servings?.max_servings ?? 0,
        protein_g: metadata?.nutrition?.protein_g ?? 0,
        fat_g: metadata?.nutrition?.fat_g ?? 0,
        carbs_g: metadata?.nutrition?.carbs_g ?? 0,
        calories: metadata?.nutrition?.calories ?? 0,
        dish_presentation_description: Array.isArray(metadata.dish_presentation_description)
            ? metadata.dish_presentation_description
            : (metadata.dish_presentation_description ? [metadata.dish_presentation_description] : []),
        steps: details.steps ?? [],
        ingredients: details.ingredients ?? [],
        matched_ingredients: typeof details.matched_ingredients === 'string'
            ? details.matched_ingredients.split(',').map(i => i.trim()).filter(Boolean)
            : (Array.isArray(details.matched_ingredients) ? details.matched_ingredients : []),
    };
}

router.post("/getImgRecipeApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_food_scan_api_logs`;
    let totalBase = "FROM Calorie_food_scan_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelImgRecipe", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_food_scan_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelImgRecipe", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_food_scan_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});

//DailyPersonal
router.post("/getDailyPersonalConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Recipe_DailyPersonalConf WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateDailyPersonalConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_DailyPersonalConf
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                top_k_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateDailyPersonalConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, id } = req.body
    const sql = `
            UPDATE Recipe_DailyPersonalConf
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                top_k = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, top_k_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getDailyPersonalVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Recipe_DailyPersonalConf`;
        let result = await mysqlQuery(sql, []);
        console.log(result)
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addDailyPersonalVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Recipe_DailyPersonalConf WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Recipe_DailyPersonalConf ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Recipe_DailyPersonalConf SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

// 批量判断哪些菜谱被当前用户收藏，返回被收藏的id数组
async function getCollectedRecipeIds(user_id, recipeIds) {
    if (!user_id || !Array.isArray(recipeIds) || recipeIds.length === 0) return [];
    const sql = `SELECT recipe_id FROM Recipe_Collect WHERE user_id = ? AND recipe_id IN (${recipeIds.map(_ => '?').join(',')})`;
    const result = await mysqlQuery(sql, [user_id, ...recipeIds]);
    return result.map(x => x.recipe_id);
}
router.post("/ai-daily-personal", async (req, res) => {
    try {
        // 解密参数length
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });

        // 请求参数
        let { user_id, language, version } = content;
        const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
        if (!rowsx || rowsx.length === 0) {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }
        // ==== 1. 新增：先查今日是否已推荐 ==== 
        const today = moment().format('YYYY-MM-DD'); // 今日日期
        const recSql = `SELECT recipe_id FROM Recipe_DailyRecommend WHERE user_id = ? AND recommend_date = ?`;
        const recResult = await mysqlQuery(recSql, [user_id, today]);
        if (recResult && recResult.length > 0) {
            // 查到今日有推荐，拿到所有recipe_id
            const recipeIds = recResult.map(r => r.recipe_id);
            if (recipeIds.length > 0) {
                // 从Recipes表取详情
                const sql = `SELECT * FROM Recipes WHERE id IN (${recipeIds.map(_ => '?').join(',')})`;
                const recipes = await mysqlQuery(sql, recipeIds);
                // 新增：查收藏
                const collectedIds = await getCollectedRecipeIds(user_id, recipeIds);
                // 遍历每条菜谱，组装新对象
                const recipesWithCollect = recipes.map(rec => {
                    // meal_time如为字符串，则split成数组，否则给空数组
                    const meal_time =
                        typeof rec.meal_time === 'string' && rec.meal_time.trim() !== ''
                            ? rec.meal_time.split(',') : [];
                    // categories同理
                    const categories =
                        typeof rec.categories === 'string' && rec.categories.trim() !== ''
                            ? rec.categories.split(',') : [];
                    // cooking_effort同理
                    const cooking_effort =
                        typeof rec.cooking_effort === 'string' && rec.cooking_effort.trim() !== ''
                            ? rec.cooking_effort.split(',') : [];
                    // dish_presentation_description同理
                    const dish_presentation_description =
                        typeof rec.dish_presentation_description === 'string' && rec.dish_presentation_description.trim() !== ''
                            ? rec.dish_presentation_description.split(',') : [];
                    const matched_ingredients =
                        typeof rec.matched_ingredients === 'string' && rec.matched_ingredients.trim() !== ''
                            ? rec.matched_ingredients.split(',') : [];
                    return {
                        ...rec, // 保留原数据
                        // 判断当前菜谱id是否被当前用户收藏
                        is_collect: collectedIds.includes(rec.id) ? true : false,
                        // steps json字符串转对象
                        steps: typeof rec.steps === 'string' ? JSON.parse(rec.steps) : rec.steps,
                        ingredients: typeof rec.ingredients === 'string' ? JSON.parse(rec.ingredients) : rec.ingredients,
                        dietary_preference: typeof rec.dietary_preference === 'string' ? JSON.parse(rec.dietary_preference) : rec.dietary_preference,
                        cuisine: typeof rec.cuisine === 'string' ? JSON.parse(rec.cuisine) : rec.cuisine,
                        total_mins: rec.total_mins || 0,
                        prep_mins: rec.prep_mins || 0,
                        cook_mins: rec.cook_mins || 0,
                        min_servings: rec.min_servings || 0,
                        max_servings: rec.max_servings || 0,
                        calories: rec.calories || 0,
                        // 新增split处理好的字段
                        meal_time,
                        categories,
                        cooking_effort,
                        dish_presentation_description,
                        matched_ingredients
                    }
                });

                // 你可根据需要再格式化下返回字段
                const encrypted = encrypt(JSON.stringify(recipesWithCollect), config.cryptoKey, config.cryptoIv);

                return res.send({
                    code: 0,
                    msg: 'success',
                    data: encrypted
                });
            }
        }
        // ==== 新增结束，没查到今日推荐才继续 ====

        // 2. 查询用户表，获取三项信息
        let userSql = `SELECT dietary_preference,allergies, cuisine, cuisine_dislike FROM Recipe_user WHERE user_id = ?`;
        let userResult = await mysqlQuery(userSql, [user_id]);
        let dietary_preference = '', cuisine = '', cuisine_dislike = '', allergies = '';
        if (userResult && userResult.length > 0) {
            dietary_preference = userResult[0].dietary_preference || '';
            cuisine = userResult[0].cuisine || '';
            cuisine_dislike = userResult[0].cuisine_dislike || '';
            allergies = userResult[0].allergies || '';
        }
        console.log('[daily-personal] 请求参数:', { user_id, language, version });
        console.log('[daily-personal] 用户偏好:', {
            dietary_preference,
            cuisine,
            cuisine_dislike,
            allergies
        });


        // 预处理参数，确保每个都是数组格式


        const cuisineArray = cuisine ? cuisine.split(',').map(item => item.trim()) : [];
        console.log('cuisineArray================', cuisineArray);

        const cuisineDislikeArray = cuisine_dislike ? cuisine_dislike.split(',').map(item => item.trim()) : [];
        console.log('cuisineDislikeArray================', cuisineDislikeArray);

        const allergiesArray = allergies ? allergies.split(',').map(item => item.trim()) : [];
        console.log('allergiesArray================', allergiesArray);

        const dietaryPreferenceArray = dietary_preference ? dietary_preference.split(',').map(item => item.trim()) : [];
        console.log('dietaryPreferenceArray================', dietaryPreferenceArray);

        console.log('[daily-personal] 处理后的参数:', {
            cuisineArray,
            cuisineDislikeArray,
            allergiesArray,
            dietaryPreferenceArray
        });

        // 新增功能：先从数据库查找匹配的菜谱
        const matchingRecipes = await findMatchingRecipesDaily(
            user_id,
            allergiesArray,
            cuisineArray,
            cuisineDislikeArray,
            dietaryPreferenceArray
        );
        console.log(`[daily-personal] 匹配到 ${matchingRecipes.length} 个菜谱`);
        // 如果找到匹配的菜谱，直接返回这些菜谱（最多3个）
        if (matchingRecipes.length > 0) {
            console.log('[daily-personal] 使用数据库匹配菜谱:', matchingRecipes.map(r => r.id));
            // 添加到用户"今日推荐"表
            for (const recipe of matchingRecipes) {
                const dailyRecommendId = utils.createId();
                const recommendDate = moment().format('YYYY-MM-DD'); // 推荐日期, 格式为 '2024-05-15'
                await mysqlQuery(
                    'INSERT IGNORE INTO Recipe_DailyRecommend (id, user_id, recipe_id, recommend_date) VALUES (?, ?, ?, ?)',
                    [dailyRecommendId, user_id, recipe.id, recommendDate]
                );
            }
            // 新增 查收藏
            const recipeIds = matchingRecipes.map(r => r.id);
            const collectedIds = await getCollectedRecipeIds(user_id, recipeIds);
            const recipesWithCollect = matchingRecipes.map(rec => ({
                ...rec,
                is_collect: collectedIds.includes(rec.id) ? true : false      // 统一用数值, 你也可以用true/false
            }));

            // 加密返回
            const encrypteds = encrypt(JSON.stringify(recipesWithCollect), config.cryptoKey, config.cryptoIv);

            return res.send({
                code: 0,
                msg: 'success',
                data: encrypteds
            });
        } else {
            console.log('[daily-personal] 未匹配到菜谱，调用AI接口生成');
        }



        // 查AI参数
        let sql = `SELECT * FROM Recipe_FoodGenConf WHERE version = ?`;
        let params = [version || '1.0.0'];
        let result = await mysqlQuery(sql, params);
        if (result.length === 0 && version) {
            sql = `SELECT * FROM Recipe_FoodGenConf ORDER BY id DESC LIMIT 1`;
            result = await mysqlQuery(sql);
        }
        if (!result.length) return res.send({ code: 2, msg: '配置不存在' });

        let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, top_k, model } = result[0];

        // 替换 prompt
        let processedPrompt = prompt_release || '';




        // 拼 messages
        const messages = [];
        messages.push({
            role: "system",
            content: [
                { type: "text", text: processedPrompt }
            ]
        });

        messages.push({
            role: "user",
            content: [
                {
                    type: "text",
                    text: `output_language:${language},cuisine:${cuisine},cuisine_dislike:${cuisine_dislike},dietary_preference:${dietary_preference},allergies:${allergies}`
                }
            ]
        });

        // 发 openai
        const response = await axios.post(
            'https://api-st.siliconflow.cn/v1/chat/completions',
            {
                model: model,
                messages,
                stream: false,
                response_format: { type: 'text' },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                top_k: Number(top_k)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer sk-jwxwusgxhligjvuqchrvjhthmwlnepwwnjyywhtynppwvdpl`
                }
            }
        );


        let message = JSON.stringify(response.data.choices[0].message);
        const messageData = JSON.parse(message);
        console.log('messageData----------------------------', messageData)
        const parsedData = extractAndParseJson(messageData.content);
        console.log('parsedData-------------', parsedData)

        const recipeList = parsedData.recipeRecommendations.recommendedRecipes;
        // 新增：用于结果返回
        const insertedRecipes = [];

        for (const recipe of recipeList) {
            if (!isValidRecipe(recipe)) {
                console.warn('[AI-IMG-RECIPE] 相似菜谱校验未通过，跳过:', similarRecipeWithDetails);
                continue;
            }
            console.log('recipe--------------------------------', recipe);
            const metadata = recipe.metadata || {};
            const details = recipe.details || {};
            const compliance = recipe.compliance || {};

            // --- 原有数据整理 ---
            const ingredientsArr = details.ingredients || [];
            const ingredientsJson = JSON.stringify(ingredientsArr);

            const allergiesSet = new Set();
            for (const ing of ingredientsArr) {
                if (Array.isArray(ing.allergies)) {
                    ing.allergies.forEach(a => allergiesSet.add(a));
                }
            }
            const allergiesJson = JSON.stringify(Array.from(allergiesSet));

            const cuisineJson = JSON.stringify(metadata.cuisine || []);
            const dietaryPreferenceJson = JSON.stringify(metadata.dietary_preference || []);
            const mealTime = Array.isArray(metadata.meal_time) ? metadata.meal_time.join(',') : (metadata.meal_time || '');
            const categories = Array.isArray(metadata.Categories) ? metadata.Categories.join(',') : (metadata.Categories || '');
            const timeObj = metadata.time || {};
            const servings = metadata.Servings || {};
            const stepsJson = details.steps ? JSON.stringify(details.steps) : null;

            // --- 入库 ---
            const id = utils.createId();
            const matchedIngredients = compliance.matchedIngredients ? compliance.matchedIngredients.join(',') : '';

            const insertSql = `
            INSERT INTO Recipes (
              id,
              english_name, translated_name, flavor, cuisine, dietary_preference, meal_time,
              categories, cooking_effort, ingredients, allergies,
              total_mins, min_servings, max_servings, prep_mins, cook_mins,
              dish_presentation_description,
              protein_g, fat_g, carbs_g, calories,
              steps, matched_ingredients, cuisine_match, diet_match
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE updated_at = NOW()
                      `;
            const values = [
                id,
                metadata.english_name || '',
                metadata.translated_name || '',
                metadata.flavor || '',
                cuisineJson,
                dietaryPreferenceJson,
                mealTime,
                categories,
                metadata.cooking_effort || '',
                ingredientsJson,
                allergiesJson,
                timeObj.total_mins || null,
                servings.min_servings || null,
                servings.max_servings || null,
                timeObj.prep_mins || null,
                timeObj.cook_mins || null,
                metadata.dish_presentation_description || '',
                (metadata.nutrition && metadata.nutrition.protein_g) || null,
                (metadata.nutrition && metadata.nutrition.fat_g) || null,
                (metadata.nutrition && metadata.nutrition.carbs_g) || null,
                (metadata.nutrition && metadata.nutrition.calories) || null,
                stepsJson,
                matchedIngredients,
                compliance.cuisineMatch ? 1 : 0,
                compliance.dietMatch ? 1 : 0,
            ];

            try {
                await mysqlQuery(insertSql, values);
                // 自动把新生成的菜谱添加到用户"今日推荐"表
                const dailyRecommendId = utils.createId();
                const recommendDate = moment().format('YYYY-MM-DD'); // 推荐日期, 格式为 '2024-05-15'

                await mysqlQuery(
                    'INSERT IGNORE INTO Recipe_DailyRecommend (id, user_id, recipe_id, recommend_date) VALUES (?, ?, ?, ?)',
                    [dailyRecommendId, user_id, id, recommendDate]
                );

                // 构造带 id、关键信息的返回对象，根据你业务需要定制
                const returnRecipe = {
                    id,  // 数据库主键
                    english_name: metadata.english_name || '',
                    translated_name: metadata.translated_name || '',
                    flavor: Array.isArray(metadata.flavor)
                        ? metadata.flavor.join(', ')
                        : (metadata.flavor || ''),
                    cuisine: metadata.cuisine || [],
                    dietary_preference: metadata.dietary_preference || [],
                    meal_time: metadata.meal_time || [],
                    categories: metadata.Categories || [],
                    cooking_effort: metadata.cooking_effort || [],
                    // servings: metadata.Servings || {},
                    // time: metadata.time || {},
                    total_mins: timeObj.total_mins || 0,
                    prep_mins: timeObj.prep_mins || 0,
                    cook_mins: timeObj.cook_mins || 0,
                    min_servings: servings.min_servings || 0,
                    max_servings: servings.max_servings || 0,
                    protein_g: metadata.nutrition.protein_g,
                    fat_g: metadata.nutrition.fat_g,
                    carbs_g: metadata.nutrition.carbs_g,
                    calories: metadata.nutrition.calories || 0,

                    dish_presentation_description: metadata.dish_presentation_description || [],
                    steps: details.steps || [],
                    ingredients: details.ingredients || [],
                    matched_ingredients: compliance.matchedIngredients || [],
                    cuisine_match: compliance.cuisineMatch ? 1 : 0,
                    diet_match: compliance.dietMatch ? 1 : 0,
                    is_collect: false,
                    img_URL: null
                };
                insertedRecipes.push(returnRecipe);

            } catch (err) {
                console.error('插入菜谱失败', err, recipe);
            }
        }


        const encrypted = encrypt(JSON.stringify(insertedRecipes), config.cryptoKey, config.cryptoIv);

        res.send({
            code: 0,
            msg: 'success',
            data: encrypted
        });

    } catch (error) {
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
});
router.post("/getDailyPersonalApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { code, dateRange, pk, uuid } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // code筛选
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    // 日期范围筛选，对应create_date字段
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("create_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // pk筛选
    if (pk) {
        sqlConditions.push("pk = ?");
        sqlParams.push(pk);
    }

    // 只查正常数据（state=0）
    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM Calorie_food_text_api_logs`;
    let totalBase = "FROM Calorie_food_text_api_logs";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY create_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            // 格式化时间字段(可选)
            if (element.create_date) {
                element.create_date = moment(element.create_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
            if (element.update_date) {
                element.update_date = moment(element.update_date)
                    .tz("Asia/Shanghai")
                    .format("YYYY-MM-DD HH:mm:ss");
            }
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});

router.post("/deleteHandelDailyPersonal", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE Calorie_food_text_api_logs SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandelDailyPersonal", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE Calorie_food_text_api_logs SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});


//Generations
router.post("/getGenerationsConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM Recipe_generations WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateGenerationsConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, model_test, negative_prompt_test, image_size_test, batch_size_test, seed_test, num_inference_steps_test, guidance_scale_test, id } = req.body
    const sql = `
            UPDATE Recipe_generations
            SET 
                prompt_test = ?, 
                model_test = ?,
                negative_prompt_test = ?,
                image_size_test = ?,
                batch_size_test = ?,
                seed_test = ?,
                num_inference_steps_test = ?,
                guidance_scale_test = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, model_test, negative_prompt_test, image_size_test, batch_size_test, seed_test, num_inference_steps_test, guidance_scale_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateGenerationsConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, model_test, negative_prompt_test, image_size_test, batch_size_test, seed_test, num_inference_steps_test, guidance_scale_test, id } = req.body
    const sql = `
            UPDATE Recipe_generations
            SET 
                prompt = ?,
                model = ?,
                negative_prompt = ?,
                image_size = ?,
                batch_size = ?,
                seed = ?,
                num_inference_steps = ?,
                guidance_scale = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, model_test, negative_prompt_test, image_size_test, batch_size_test, seed_test, num_inference_steps_test, guidance_scale_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getGenerationsVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM Recipe_generations`;
        let result = await mysqlQuery(sql, []);
        console.log(result)
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addGenerationsVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM Recipe_generations WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM Recipe_generations ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO Recipe_generations SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})
router.post('/ai-generations', async (req, res) => {
    try {
        let parseData = req.body.query;
        let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
        const chinaTime = new Date();
        console.log(content, 'content', chinaTime.toLocaleString());

        if (content.code !== 'OZqcJQLBUyevTh') return res.send({ code: 1, msg: 'code错误' });
        const { version, dish_name, main_ingredients, dish_presentation_description, user_id } = content;
        const rowsx = await mysqlQuery(`SELECT * FROM Recipe_user WHERE user_id = ?`, [user_id]);
        if (!rowsx || rowsx.length === 0) {
            return res.status(404).json({ code: 404, message: "用户不存在" });
        }
        let url = await generations(version, dish_name, main_ingredients, dish_presentation_description)
        const encrypted = encrypt(JSON.stringify(url), config.cryptoKey, config.cryptoIv);

        res.send({
            code: 0,
            msg: 'success',
            url: encrypted
        })
    } catch (error) {
        console.error('AI Food Text error:', error?.response?.data || error.message);
        res.send({
            code: 1,
            msg: error?.response?.data || error.message,
        });
    }
})
router.post('/setImageUrl', async (req, res) => {
    const { recipe_id, ImageUrl } = req.body;

    if (!recipe_id || !ImageUrl) {
        return res.status(400).json({ code: 400, msg: 'recipe_id 和 ImageUrl 都不能为空~' });
    }

    try {
        // 检查菜谱是否存在
        const rows = await mysqlQuery('SELECT * FROM Recipes WHERE id = ?', [recipe_id]);
        if (!rows || rows.length === 0) {
            return res.status(404).json({ code: 404, msg: '菜谱不存在' });
        }

        // 更新 img_URL 字段
        await mysqlQuery(
            'UPDATE Recipes SET img_URL = ? WHERE id = ?',
            [ImageUrl, recipe_id]
        );

        // 可返回更新后的数据或简单成功提示
        return res.send({ code: 0, msg: 'success' });
    } catch (error) {
        console.error('[setImageUrl] 更新图片链接失败:', error);
        return res.status(500).json({ code: 500, msg: '服务器异常，请稍后再试！' });
    }
});
async function generations(version, dish_name, main_ingredients, dish_presentation_description) {
    // 查AI参数
    let sql = `SELECT * FROM Recipe_generations WHERE version = ?`;
    let params = [version || '1.0.0'];
    let result = await mysqlQuery(sql, params);
    if (result.length === 0 && version) {
        sql = `SELECT * FROM Recipe_generations ORDER BY id DESC LIMIT 1`;
        result = await mysqlQuery(sql);
    }
    if (!result.length) throw new Error('配置不存在'); // 建议直接抛异常，接口里捕获处理

    let { prompt, model, negative_prompt, image_size, batch_size, seed, num_inference_steps, guidance_scale } = result[0];
    // 占位符替换
    let processedPrompt = prompt;
    if (dish_name) {
        processedPrompt = processedPrompt.replace(/\[@@@@@\]/g, dish_name);
    }
    if (main_ingredients) {
        processedPrompt = processedPrompt.replace(/\[#####\]/g, main_ingredients);
    }
    if (dish_presentation_description) {
        processedPrompt = processedPrompt.replace(/\[%%%%%\]/g, dish_presentation_description);
    }
    console.log(processedPrompt)

    // 构造请求体，只添加有值的 key
    let bodyObj = {};
    bodyObj.prompt = processedPrompt;
    if (model) bodyObj.model = model;
    if (negative_prompt) bodyObj.negative_prompt = negative_prompt;
    if (image_size) bodyObj.image_size = image_size;
    if (batch_size !== '' && batch_size !== null && batch_size !== undefined) bodyObj.batch_size = Number(batch_size);
    if (seed !== '' && seed !== null && seed !== undefined) bodyObj.seed = Number(seed);
    if (num_inference_steps !== '' && num_inference_steps !== null && num_inference_steps !== undefined) bodyObj.num_inference_steps = Number(num_inference_steps);
    if (guidance_scale !== '' && guidance_scale !== null && guidance_scale !== undefined) bodyObj.guidance_scale = Number(guidance_scale);

    try {
        // 1. 调用AI图生成接口
        const aiRes = await axios.post(
            "https://api-st.siliconflow.cn/v1/images/generations",
            bodyObj,
            {
                headers: {
                    "Content-Type": "application/json",
                    Authorization: `Bearer sk-jwxwusgxhligjvuqchrvjhthmwlnepwwnjyywhtynppwvdpl`
                }
            }
        );
        if (!aiRes.data || !aiRes.data.data || !aiRes.data.data.length) {
            throw new Error("AI接口没有返回图片");
        }
        const imageUrl = aiRes.data.data[0].url;
        // console.log(imageUrl, 'imageUrl')

        // // 2. 立即下载并转存成自己服务器的永久地址
        // const permUrl = await saveTempImageToLocal(imageUrl);

        // 3. 返回永久图片地址
        return imageUrl;

    } catch (err) {

        // 错误处理
        console.error('AI生成图片失败', err);
        throw err;
    }
}

/**
 * 下载一个远程图片到本地 /home/wwwroot/default/RecipeMedia/ 目录，并返回服务器可访问 url
 * @param {string} imageUrl 临时图片url（外部AI平台生成的url）
 * @returns {string} 服务器可访问 url（如 https://pdf.seamobitech.com/CalorieMedia/xxx.png）
 */
async function saveTempImageToLocal(imageUrl) {
    // 1. 下载图片为 buffer
    const response = await axios.get(imageUrl, { responseType: "arraybuffer" });
    console.log(response, 'response')
    const buffer = Buffer.from(response.data, "binary");

    // 2. 生成目标目录和文件名
    const ext = path.extname(imageUrl) || '.png'; // 使用原扩展名，若无默认用png
    const fileName = `${Date.now()}-ai${Math.random().toString(36).slice(2)}${ext}`;
    const saveDir = "/home/wwwroot/default/RecipeMedia";      // 你的固定位上传目录
    if (!fs.existsSync(saveDir)) {
        fs.mkdirSync(saveDir, { recursive: true });
    }
    const filePath = path.join(saveDir, fileName);

    // 3. 写入文件
    fs.writeFileSync(filePath, buffer);

    // 4. 返回服务器访问地址（与你上传接口保持一致）
    const fileUrl = `https://pdf.seamobitech.com/CalorieMedia/${fileName}`;
    console.log(fileUrl, 'fileUrl')

    return fileUrl;
}


// 删除文件接口
router.post("/deleteFile", (req, res) => {
    // 建议用 POST，防止恶意随机拼接口；参数用 req.body
    const { filename } = req.body;
    console.log(filename)
    if (!filename) {
        return res.status(400).json({ code: 1, message: '缺少文件名参数！' });
    }

    // 检查文件名是否有目录穿越，防止安全漏洞
    if (filename.includes('/') || filename.includes('\\')) {
        return res.status(400).json({ code: 2, message: '非法文件名！' });
    }

    const dir = "/home/wwwroot/default/RecipeMedia";
    const filePath = path.join(dir, filename);

    fs.unlink(filePath, (err) => {
        if (err) {
            // 文件不存在会出错等
            if (err.code === 'ENOENT') {
                return res.status(404).json({ code: 3, message: '文件不存在！' });
            }
            return res.status(500).json({ code: 500, message: '删除失败', error: err.message });
        }
        res.json({ code: 0, message: 'success' });
    });
});


// 通用安全删除函数
function safeDeleteImage(imageUrl) {
    const prefix = 'https://pdf.seamobitech.com/RecipeMedia/';
    const dir = "/home/wwwroot/default/RecipeMedia";
    if (!imageUrl || typeof imageUrl !== 'string') return;
    // 只删允许前缀
    if (!imageUrl.startsWith(prefix)) return;
    // 取文件名
    const filename = imageUrl.replace(prefix, '');
    if (!filename || filename.includes('/') || filename.includes('\\')) return; // 防目录穿越
    const filePath = path.join(dir, filename);
    fs.unlink(filePath, (err) => {
        if (err && err.code !== 'ENOENT') {
            console.error('[safeDeleteImage] 删除失败:', filePath, err.message);
        } else {
            console.log('[safeDeleteImage] 已删除:', filePath);
        }
    });
}
// const cheerio = require('cheerio');

router.post('/run', async (req, res) => {
    // const recipeObj = await getAllrecipesRecipe('https://www.allrecipes.com/recipe/281618/watermelon-vodka-spritzer/');
    //     await saveRecipe(recipeObj);
    runBatchFromSitemapXml('https://www.allrecipes.com/sitemap_1.xml')
})


// async function saveRecipexxx(recipeObj) {
//     try {
//         // --- 数据准备 ---
//         const id = utils.createId();
//         const insertSql = `
// INSERT INTO Recipes (
//     id, english_name, translated_name, flavor, cuisine, dietary_preference, meal_time,
//     categories, cooking_effort, ingredients, allergies,
//     total_mins, min_servings, max_servings, prep_mins, cook_mins,
//     dish_presentation_description,
//     protein_g, fat_g, carbs_g, calories,
//     steps, matched_ingredients, cuisine_match, diet_match,
//     img_URL
// ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
// ON DUPLICATE KEY UPDATE updated_at = NOW()
// `;

//         // 处理可能为 null 的自动序列化字段
//         function toStr(v) {
//             return v == null ? null : v.toString();
//         }
//         // 确保 JSON 字段永远为 JSON 字符串
//         function toJsonStr(v) {
//             return v == null ? JSON.stringify([]) : toStr(v);
//         }

//         const values = [
//             id,
//             recipeObj.english_name || '',
//             recipeObj.translated_name || '',
//             toStr(recipeObj.flavor) || '',
//             toStr(recipeObj.cuisine) || JSON.stringify(['others']),
//             toStr(recipeObj.dietary_preference) || null,
//             toStr(recipeObj.meal_time) || null,
//             toStr(recipeObj.categories) || '',
//             toStr(recipeObj.cooking_effort) || '',
//             recipeObj.ingredients || JSON.stringify([]),
//             recipeObj.allergies || null,
//             recipeObj.total_mins || null,
//             recipeObj.min_servings || null,
//             recipeObj.max_servings || null,
//             recipeObj.prep_mins || null,
//             recipeObj.cook_mins || null,
//             recipeObj.dish_presentation_description || null,
//             recipeObj.protein_g || null,
//             recipeObj.fat_g || null,
//             recipeObj.carbs_g || null,
//             recipeObj.calories || null,
//             recipeObj.steps || JSON.stringify([]),
//             recipeObj.matched_ingredients || '',
//             recipeObj.cuisine_match || 0,
//             recipeObj.diet_match || 0,
//             recipeObj.img_URL || null
//         ];

//         await mysqlQuery(insertSql, values);
//         return id;
//     } catch (err) {
//         console.error('[saveRecipe] 插入菜谱失败:', err);
//         return null;
//     }
// }
// 工具函数
function toMinutes(iso) {
    let min = 0;
    if (!iso) return null;
    let m = iso.match(/(\d+)H/);
    if (m) min += parseInt(m[1]) * 60;
    m = iso.match(/(\d+)M/);
    if (m) min += parseInt(m[1]);
    return min || null;
}
function parseNumber(val) {
    if (!val) return null;
    const m = /([\d.]+)/.exec(val);
    return m ? parseFloat(m[1]) : null;
}
function parseIngredientsHtml($) {
    const result = [];
    $('ul.mm-recipes-structured-ingredients__list > li.mm-recipes-structured-ingredients__list-item').each((i, el) => {
        let quantity = $(el).find('span[data-ingredient-quantity="true"]').text().trim();
        let unit = $(el).find('span[data-ingredient-unit="true"]').text().trim();
        let name = $(el).find('span[data-ingredient-name="true"]').text().trim();
        let quantityFull = ((quantity ? quantity + ' ' : '') + (unit || '')).trim();
        result.push({
            quantity: quantityFull,
            allergies: [],
            ingredient: name
        });
    });
    return result;
}

async function getAllrecipesRecipe(url) {
    const res = await axios.get(url, { timeout: 20000 });
    const $ = cheerio.load(res.data);

    let recipeJson = null;
    $('script[type="application/ld+json"]').each((i, el) => {
        try {
            const jsonArr = JSON.parse($(el).html());
            if (Array.isArray(jsonArr)) {
                const recipeObj = jsonArr.find(j => j['@type'] && (j['@type'] === 'Recipe' || (Array.isArray(j['@type']) && j['@type'].includes('Recipe'))));
                if (recipeObj) recipeJson = recipeObj;
            }
        } catch (e) { }
    });

    if (!recipeJson) { console.log('未找到菜谱json'); return; }

    // 优先提取结构化 ingredients
    let ingredientsArr = parseIngredientsHtml($);
    if (!ingredientsArr || ingredientsArr.length === 0) {
        ingredientsArr = (recipeJson.recipeIngredient || []).map(i => ({
            quantity: '',
            allergies: [],
            ingredient: i
        }));
    }

    let stepsArr = [];
    if (Array.isArray(recipeJson.recipeInstructions)) {
        stepsArr = recipeJson.recipeInstructions.map((st, idx) => {
            let action = '';
            let proTip = '';
            let equipment = '';
            if (typeof st === "string") {
                action = st.trim();
            } else if (typeof st === "object") {
                action = st.text || st.name || '';
                proTip = st.proTip || st.tip || '';
                if (st.tool) {
                    equipment = Array.isArray(st.tool)
                        ? st.tool.map(tool => tool.name || tool).join(',')
                        : (st.tool.name || st.tool || '');
                }
            }
            return {
                action: action.trim(),
                proTip: proTip.trim(),
                sequence: idx + 1,
                equipment: equipment
            };
        }).filter(st => st.action);
    } else if (typeof recipeJson.recipeInstructions === 'string') {
        stepsArr = recipeJson.recipeInstructions
            .split(/(?:\r?\n|\.\s+)/)
            .map((t, i) => ({
                action: t.trim(),
                proTip: '',
                sequence: i + 1,
                equipment: ''
            }))
            .filter(st => st.action);
    }

    // 生成唯一ID（用url或sha1都可以，这里简单用链接最后的数字或者时间戳）
    let idGen = () => {
        let m = url.match(/\/(\d+)\//);
        return m ? m[1] : (new Date().getTime() + Math.floor(Math.random() * 10000)); //万一没有数字ID，退化成时间戳+随机数
    };

    // obj全字段，未知填null
    const obj = {
        // 主键等
        id: idGen(),  //varchar(64)
        english_name: recipeJson.name || null,
        translated_name: null,
        flavor: recipeJson.keywords || null,

        cuisine: (() => {
            if (Array.isArray(recipeJson.recipeCuisine) && recipeJson.recipeCuisine.length > 0) {
                return JSON.stringify(recipeJson.recipeCuisine);
            }
            if (typeof recipeJson.recipeCuisine === 'string' && recipeJson.recipeCuisine.trim() !== '') {
                return JSON.stringify([recipeJson.recipeCuisine]);
            }
            return JSON.stringify(['others']);
        })(),
        dietary_preference: null, //无

        meal_time: null,
        categories: Array.isArray(recipeJson.recipeCategory) ? recipeJson.recipeCategory.join(',') : (recipeJson.recipeCategory || null),
        cooking_effort: null,

        ingredients: JSON.stringify(ingredientsArr),
        allergies: null,  //可后续自动标注

        total_mins: recipeJson.totalTime ? toMinutes(recipeJson.totalTime) : null,
        min_servings: (() => {
            if (Array.isArray(recipeJson.recipeYield))
                return parseInt(recipeJson.recipeYield[0]);
            if (typeof recipeJson.recipeYield === 'string') {
                let m = recipeJson.recipeYield.match(/(\d+)/);
                if (m) return parseInt(m[1]);
            }
            return null;
        })(),
        max_servings: recipeJson.recipeYield ? parseNumber(recipeJson.recipeYield[0]) : null,

        prep_mins: recipeJson.prepTime ? toMinutes(recipeJson.prepTime) : null,
        cook_mins: recipeJson.cookTime ? toMinutes(recipeJson.cookTime) : null,
        dish_presentation_description: null,

        protein_g: recipeJson.nutrition?.proteinContent ? parseNumber(recipeJson.nutrition?.proteinContent) : null,
        fat_g: recipeJson.nutrition?.fatContent ? parseNumber(recipeJson.nutrition?.fatContent) : null,
        carbs_g: recipeJson.nutrition?.carbohydrateContent ? parseNumber(recipeJson.nutrition?.carbohydrateContent) : null,
        calories: recipeJson.nutrition?.calories ? parseNumber(recipeJson.nutrition?.calories) : null,

        steps: JSON.stringify(stepsArr),
        matched_ingredients: null,
        cuisine_match: 0,
        diet_match: 0,

        created_at: null,
        updated_at: null,
        img_URL: typeof recipeJson.image === 'string'
            ? recipeJson.image
            : (recipeJson.image?.url || null)
    };
    console.log(obj);
    return obj;
}

// 示例用法
// (async function () {
//     const recipeObj = await getAllrecipesRecipe('https://www.allrecipes.com/recipe/281618/watermelon-vodka-spritzer/');
//     await saveRecipe(recipeObj);
//     // 你还可以 console.log 或其它后续逻辑
// })();
const xml2js = require('xml2js');
async function getUrlsFromSitemap(sitemapUrl) {
    console.log('开始请求 sitemap:', sitemapUrl);
    try {
        const res = await axios.get(sitemapUrl, { timeout: 30000 });
        const xml = res.data;
        const obj = await xml2js.parseStringPromise(xml);
        const urls = [];
        if (obj.urlset && obj.urlset.url) {
            for (const urlNode of obj.urlset.url) {
                if (urlNode.loc && urlNode.loc[0]) {
                    urls.push(urlNode.loc[0]);
                }
            }
        }
        return urls;
    } catch (err) {
        console.error('[getUrlsFromSitemap] 错误:', err.message);
        return [];
    }
}


// 批量爬取并入库
async function runBatchFromSitemapXml(sitemapUrl) {
    const allUrls = await getUrlsFromSitemap(sitemapUrl);
    console.log('共提取到', allUrls.length, '个URL');
    let success = 0, fail = 0;
    for (let i = 0; i < allUrls.length; i++) {
        const url = allUrls[i];
        try {
            console.log(`[${i + 1}/${allUrls.length}] 爬取: ${url}`);
            const recipeObj = await getAllrecipesRecipe(url);
            if (!recipeObj) {
                console.log(`⛔️ 失败: 无法解析菜谱对象 (跳过)`);
                fail++;
                continue;
            }
            await saveRecipe(recipeObj);
            console.log('✅ 成功');
            success++;
        } catch (e) {
            console.log('⛔️ 爬取或入库失败，原因：', e && e.message);
            fail++;
        }
    }
    console.log(`批量结束，成功 ${success} 个，失败 ${fail} 个`);
}
router.post('/getRecipeDetail', async (req, res) => {
    const { recipe_id } = req.body;

    if (!recipe_id) {
        return res.status(400).json({ code: 400, msg: 'Missing recipe_id' });
    }

    try {
        const results = await mysqlQuery('SELECT * FROM Recipes WHERE id = ?', [recipe_id]);
        if (!results || results.length === 0) {
            return res.status(404).json({ code: 404, msg: 'Recipe not found' });
        }

        // 拿出第一条记录
        const recipe = results[0];

        // 解析JSON字段，避免前端解析出错
        ['cuisine', 'dietary_preference', 'ingredients', 'allergies', 'steps'].forEach(field => {
            if (recipe[field] && typeof recipe[field] === 'string') {
                try { recipe[field] = JSON.parse(recipe[field]); } catch (e) {/*ignore*/ }
            }
        });

        res.json({
            code: 0,
            msg: 'ok',
            data: recipe
        });
    } catch (err) {
        console.error('getRecipeDetail接口报错', err);
        res.status(500).json({ code: 500, msg: 'Database error', error: err.toString() });
    }
});
// 启动爬虫任务
// runBatchFromSitemapXml('https://www.allrecipes.com/sitemap_1.xml');



// 周计划接口：传base_date，自动查自然周
// router.post('/week-plan/generate', async function generateWeekPlan(req, res) {
//     try {
//         // 1. 参数预处理
//         let { user_id, base_date, meal_times = '', min_servings, max_servings } = req.body;
//         if (typeof meal_times === 'string') {
//             meal_times = meal_times.split(',').map(i => i.trim()).filter(Boolean);
//         }
//         if (!user_id || !base_date || meal_times.length === 0) {
//             return res.status(400).json({ error: '缺少 user_id/base_date/meal_times 参数' });
//         }
//         console.log('[参数] user_id:', user_id, 'base_date:', base_date, 'meal_times:', meal_times);

//         // 2. 用户信息
//         const userRows = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id=?', [user_id]);
//         if (!userRows || userRows.length === 0) {
//             return res.status(404).json({ error: '未找到用户' });
//         }
//         const user = userRows[0];

//         // 3. 查 base_date 所在自然周周一~周日
//         const baseMoment = moment(base_date, 'YYYY-MM-DD');
//         const weekStart = baseMoment.clone().isoWeekday(1).format('YYYY-MM-DD');
//         const weekEnd = baseMoment.clone().isoWeekday(7).format('YYYY-MM-DD');

//         // 4. 优先查本周是否已有计划（哪天有都算有）
//         const weekExistPlans = await mysqlQuery(
//             `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
//             [user_id, weekStart, weekEnd]
//         );
//         if (weekExistPlans.length > 0) {
//             // 只要有数据，直接返回整周所有计划
//             console.log('[已有计划]本周有计划，直接返回:', weekExistPlans.length);
//             return await formatAndSendPlans(weekExistPlans, res);
//         }
//         // >>>>>>>>> 新增：base_date落在今天之前，禁止新建
//         const todayStr = moment().format('YYYY-MM-DD');
//         const baseStr = moment(base_date, 'YYYY-MM-DD').format('YYYY-MM-DD');
//         if (baseStr < todayStr) {
//             console.log('[禁止生成历史周计划]: base_date早于今天', baseStr, '<', todayStr);
//             return res.send({ code: 0, data: {} });
//         }
//         // <<<<<<<<<
//         // 5. 只生成 base_date ~ 周日
//         const dayOfWeek = baseMoment.isoWeekday();
//         const weekDates = [];
//         for (let i = 0; i < 8 - dayOfWeek; i++) {
//             weekDates.push(baseMoment.clone().add(i, 'days').format('YYYY-MM-DD'));
//         }
//         console.log('[需新生成日期]:', weekDates);

//         // --------- 用户喜好参数 ------------
//         const cuisine_like = safeParseArr(user.cuisine);
//         const cuisine_dislike = safeParseArr(user.cuisine_dislike);
//         const cuisine_left = safeParseArr(user.cuisine_left);
//         const allergies = safeParseArr(user.allergies);

//         const userMinServings = user.min_servings;
//         const userMaxServings = user.max_servings;
//         let dinnerMinServings = (min_servings !== undefined && min_servings !== null)
//             ? Number(min_servings)
//             : (userMinServings !== undefined && userMinServings !== null ? Number(userMinServings) : 3);
//         let dinnerMaxServings = (max_servings !== undefined && max_servings !== null)
//             ? Number(max_servings)
//             : (userMaxServings !== undefined && userMaxServings !== null ? Number(userMaxServings) : null);

//         // --------- 新生成逻辑 -----------
//         const planToInsert = [];
//         for (let meal of meal_times) {
//             const daysN = weekDates.length;

//             const receNeed = (meal === 'dinner') ? daysN * 2 : daysN;

//             let whereBase = '1=1 ';
//             if (cuisine_dislike.length > 0) {
//                 whereBase += 'AND NOT (' +
//                     cuisine_dislike.map(cd => `JSON_CONTAINS(cuisine, '"${cd}"')`).join(' OR ') +
//                     ') ';
//             }
//             if (allergies.length > 0) {
//                 whereBase += 'AND (allergies IS NULL ' +
//                     'OR ' +
//                     allergies.map(alg => `NOT JSON_CONTAINS(allergies, '"${alg}"')`).join(' AND ') +
//                     ') ';
//             }
//             let whereCuisineLike = '';
//             if (cuisine_like.length > 0) {
//                 whereCuisineLike = 'AND (' +
//                     cuisine_like.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') +
//                     ') ';
//             } else if (cuisine_left.length > 0) {
//                 whereCuisineLike = 'AND (' +
//                     cuisine_left.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') +
//                     ') ';
//             }
//             let extraWhere = '';
//             if (meal === 'breakfast') {
//                 extraWhere += 'AND (total_mins IS NULL OR total_mins <= 20)';
//             } else if (meal === 'lunch') {
//                 extraWhere += 'AND (total_mins BETWEEN 10 AND 30)';
//             } else if (meal === 'dinner') {
//                 extraWhere += 'AND (total_mins >= 30)';
//                 if (!isNaN(dinnerMinServings)) {
//                     extraWhere += ` AND max_servings >= ${dinnerMinServings}`;
//                 }
//                 if (dinnerMaxServings !== null && !isNaN(dinnerMaxServings)) {
//                     extraWhere += ` AND max_servings <= ${dinnerMaxServings}`;
//                 }
//             }
//             let sqlWhere = whereBase + whereCuisineLike + extraWhere;
//             let recipeSql = `SELECT * FROM Recipes WHERE ${sqlWhere}`;
//             const recipes = await mysqlQuery(recipeSql, []);
//             // 随机选菜
//             let randomRecipes = [];
//             if (recipes.length > receNeed) {
//                 const arr = [...recipes];
//                 for (let i = 0; i < receNeed; i++) {
//                     const idx = Math.floor(Math.random() * arr.length);
//                     randomRecipes.push(arr[idx]);
//                     arr.splice(idx, 1);
//                 }
//             } else {
//                 randomRecipes = recipes;
//             }

//             // 分配到每一天
//             const dailyArr = Array(daysN).fill(0).map(() => []);
//             let tmpRecipes = [...randomRecipes];
//             for (let i = 0; i < receNeed && tmpRecipes.length > 0; i++) {
//                 let dayIdx = i % daysN;
//                 if (meal === 'dinner') {
//                     if (dailyArr[dayIdx].length >= 2) {
//                         for (let di = 0; di < daysN; di++) {
//                             if (dailyArr[di].length < 2) { dayIdx = di; break; }
//                         }
//                     }
//                 } else {
//                     if (dailyArr[dayIdx].length >= 1) {
//                         for (let di = 0; di < daysN; di++) {
//                             if (dailyArr[di].length < 1) { dayIdx = di; break; }
//                         }
//                     }
//                 }
//                 const randIdx = Math.floor(Math.random() * tmpRecipes.length);
//                 const recipe = tmpRecipes[randIdx];
//                 dailyArr[dayIdx].push(recipe);
//                 tmpRecipes.splice(randIdx, 1);
//             }
//             for (let i = 0; i < daysN; i++) {
//                 for (let r of dailyArr[i]) {
//                     if (r) {
//                         planToInsert.push({
//                             id: utils.createId(),
//                             user_id: user_id,
//                             recipe_id: r.id,
//                             meal_date: weekDates[i],
//                             meal_time: meal
//                         });
//                     }
//                 }
//             }
//         }

//         // 插入本批计划
//         if (planToInsert.length > 0) {
//             const insertSql = `
//                 INSERT INTO Recipe_week_plan
//                   (id, user_id, recipe_id, meal_date, meal_time)
//                 VALUES ${planToInsert.map(() => '(?,?,?,?,?)').join(',')}
//             `;
//             const params = planToInsert.flatMap(item =>
//                 [item.id, item.user_id, item.recipe_id, item.meal_date, item.meal_time]
//             );
//             await mysqlQuery(insertSql, params);
//         }

//         // 最后查本周所有并返回
//         const weekFullPlans = await mysqlQuery(
//             `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
//             [user_id, weekStart, weekEnd]
//         );
//         return await formatAndSendPlans(weekFullPlans, res);

//     } catch (err) {
//         console.error('生成周计划出错', err);
//         return res.status(500).json({ error: '服务端出错' });
//     }
// });

// router.post('/week-plan/generate-by-ingredient', async function generateWeekPlanByIngredient(req, res) {
//     try {
//         // 1. 参数预处理
//         let { user_id, base_date, meal_times = '', min_servings, max_servings, ingredients = '' } = req.body;
//         if (typeof meal_times === 'string') {
//             meal_times = meal_times.split(',').map(i => i.trim()).filter(Boolean);
//         }
//         if (!user_id || !base_date || meal_times.length === 0) {
//             return res.status(400).json({ error: '缺少 user_id/base_date/meal_times 参数' });
//         }

//         // 解析 ingredients 字符串为数组（支持中文逗号）
//         let ingredientsArray = [];
//         if (typeof ingredients === 'string') {
//             ingredientsArray = ingredients.replace(/，/g, ',').split(',').map(s => s.trim()).filter(Boolean);
//         } else if (Array.isArray(ingredients)) {
//             ingredientsArray = ingredients.join(',').replace(/，/g, ',').split(',').map(s => s.trim()).filter(Boolean);
//         }
//         console.log('[食材参数] ingredientsArray:', ingredientsArray);

//         // 2. 用户信息
//         const userRows = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id=?', [user_id]);
//         if (!userRows || userRows.length === 0) {
//             return res.status(404).json({ error: '未找到用户' });
//         }
//         const user = userRows[0];

//         // 3. 查 base_date 所在自然周周一~周日，已生成过则直接查全周
//         const baseMoment = moment(base_date, 'YYYY-MM-DD');
//         const weekStart = baseMoment.clone().isoWeekday(1).format('YYYY-MM-DD');
//         const weekEnd = baseMoment.clone().isoWeekday(7).format('YYYY-MM-DD');
//         const weekExistPlans = await mysqlQuery(
//             `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
//             [user_id, weekStart, weekEnd]
//         );
//         if (weekExistPlans.length > 0) {
//             return await formatAndSendPlans(weekExistPlans, res);
//         }
//         // >>>>>>>>> 新增：base_date落在今天之前，禁止新建
//         const todayStr = moment().format('YYYY-MM-DD');
//         const baseStr = moment(base_date, 'YYYY-MM-DD').format('YYYY-MM-DD');
//         if (baseStr < todayStr) {
//             console.log('[禁止生成历史周计划]: base_date早于今天', baseStr, '<', todayStr);
//             return res.send({ code: 0, data: {} });
//         }
//         // <<<<<<<<<
//         // 4. 拼接本次要生成（base_date~周日）的全部日期
//         const dayOfWeek = baseMoment.isoWeekday();
//         const weekDates = [];
//         for (let i = 0; i < 8 - dayOfWeek; i++) {
//             weekDates.push(baseMoment.clone().add(i, 'days').format('YYYY-MM-DD'));
//         }
//         console.log('[需新生成日期]:', weekDates);

//         // 5. 用户偏好、菜系、过敏
//         const cuisine_like = safeParseArr(user.cuisine);
//         const cuisine_dislike = safeParseArr(user.cuisine_dislike);
//         const cuisine_left = safeParseArr(user.cuisine_left);
//         const allergies = safeParseArr(user.allergies);

//         const userMinServings = user.min_servings;
//         const userMaxServings = user.max_servings;
//         let dinnerMinServings = (min_servings !== undefined && min_servings !== null)
//             ? Number(min_servings)
//             : (userMinServings !== undefined && userMinServings !== null ? Number(userMinServings) : 3);
//         let dinnerMaxServings = (max_servings !== undefined && max_servings !== null)
//             ? Number(max_servings)
//             : (userMaxServings !== undefined && userMaxServings !== null ? Number(userMaxServings) : null);

//         // 6. 新生成三餐并进行ingredientsArray模糊筛选


//         const planToInsert = [];
//         for (let meal of meal_times) {
//             const daysN = weekDates.length;
//             const receNeed = (meal === 'dinner') ? daysN * 2 : daysN;

//             let whereBase = '1=1 ';
//             if (cuisine_dislike.length > 0) {
//                 whereBase += 'AND NOT (' +
//                     cuisine_dislike.map(cd => `JSON_CONTAINS(cuisine, '"${cd}"')`).join(' OR ') + ') ';
//             }
//             if (allergies.length > 0) {
//                 whereBase += 'AND (allergies IS NULL ' + 'OR ' +
//                     allergies.map(alg => `NOT JSON_CONTAINS(allergies, '"${alg}"')`).join(' AND ') + ') ';
//             }
//             let whereCuisineLike = '';
//             if (cuisine_like.length > 0) {
//                 whereCuisineLike = 'AND (' +
//                     cuisine_like.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') + ') ';
//             } else if (cuisine_left.length > 0) {
//                 whereCuisineLike = 'AND (' +
//                     cuisine_left.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') + ') ';
//             }
//             let extraWhere = '';
//             if (meal === 'breakfast') {
//                 extraWhere += 'AND (total_mins IS NULL OR total_mins <= 20)';
//             } else if (meal === 'lunch') {
//                 extraWhere += 'AND (total_mins BETWEEN 10 AND 30)';
//             } else if (meal === 'dinner') {
//                 extraWhere += 'AND (total_mins >= 30)';
//                 if (!isNaN(dinnerMinServings)) {
//                     extraWhere += ` AND max_servings >= ${dinnerMinServings}`;
//                 }
//                 if (dinnerMaxServings !== null && !isNaN(dinnerMaxServings)) {
//                     extraWhere += ` AND max_servings <= ${dinnerMaxServings}`;
//                 }
//             }
//             let sqlWhere = whereBase + whereCuisineLike + extraWhere;
//             let recipeSql = `SELECT * FROM Recipes WHERE ${sqlWhere}`;
//             const recipes = await mysqlQuery(recipeSql, []);
//             console.log(`[${meal}] 总备选菜数（未食材过滤）:`, recipes.length);

//             // —— ingredientsArray模糊包含匹配，不少于2种
//             let recipesByIngredient = recipes.filter(row => {
//                 let recipeIngredients = [];
//                 try { recipeIngredients = JSON.parse(row.ingredients || '[]'); } catch { }
//                 let matchedSet = new Set();
//                 if (Array.isArray(recipeIngredients) && ingredientsArray.length > 0) {
//                     for (const userIngredient of ingredientsArray) {
//                         const userIngredientSafe = userIngredient.trim().toLowerCase();
//                         if (!userIngredientSafe) continue;
//                         const matched = recipeIngredients.some(ingredient => {
//                             let ingredientName = '';
//                             if (typeof ingredient === 'string') {
//                                 ingredientName = ingredient;
//                             } else if (ingredient && typeof ingredient === 'object') {
//                                 ingredientName = ingredient.name || ingredient.ingredient || '';
//                             }
//                             return ingredientName && ingredientName.toLowerCase().includes(userIngredientSafe);
//                         });
//                         if (matched) matchedSet.add(userIngredientSafe);
//                     }
//                 }
//                 return matchedSet.size >= 2;
//             });
//             console.log(`[${meal}] 通过食材模糊包含过滤后剩余:`, recipesByIngredient.length);
//             // 原始生成算法
//             let randomRecipes = [];
//             if (recipesByIngredient.length > receNeed) {
//                 const arr = [...recipesByIngredient];
//                 for (let i = 0; i < receNeed; i++) {
//                     const idx = Math.floor(Math.random() * arr.length);
//                     randomRecipes.push(arr[idx]);
//                     arr.splice(idx, 1);
//                 }
//             } else {
//                 randomRecipes = recipesByIngredient;
//             }

//             // 分配到每一天
//             const dailyArr = Array(daysN).fill(0).map(() => []);
//             let tmpRecipes = [...randomRecipes];
//             for (let i = 0; i < receNeed && tmpRecipes.length > 0; i++) {
//                 let dayIdx = i % daysN;
//                 if (meal === 'dinner') {
//                     if (dailyArr[dayIdx].length >= 2) {
//                         for (let di = 0; di < daysN; di++) {
//                             if (dailyArr[di].length < 2) { dayIdx = di; break; }
//                         }
//                     }
//                 } else {
//                     if (dailyArr[dayIdx].length >= 1) {
//                         for (let di = 0; di < daysN; di++) {
//                             if (dailyArr[di].length < 1) { dayIdx = di; break; }
//                         }
//                     }
//                 }
//                 const randIdx = Math.floor(Math.random() * tmpRecipes.length);
//                 const recipe = tmpRecipes[randIdx];
//                 dailyArr[dayIdx].push(recipe);
//                 tmpRecipes.splice(randIdx, 1);
//             }
//             for (let i = 0; i < daysN; i++) {
//                 for (let r of dailyArr[i]) {
//                     if (r) {
//                         planToInsert.push({
//                             id: utils.createId(),
//                             user_id: user_id,
//                             recipe_id: r.id,
//                             meal_date: weekDates[i],
//                             meal_time: meal
//                         });
//                     }
//                 }
//             }
//         }

//         // 插入本批计划
//         if (planToInsert.length > 0) {
//             const insertSql = `
//                 INSERT INTO Recipe_week_plan
//                   (id, user_id, recipe_id, meal_date, meal_time)
//                 VALUES ${planToInsert.map(() => '(?,?,?,?,?)').join(',')}
//             `;
//             const params = planToInsert.flatMap(item =>
//                 [item.id, item.user_id, item.recipe_id, item.meal_date, item.meal_time]
//             );
//             await mysqlQuery(insertSql, params);
//         }

//         // 查本周所有并返回
//         const weekFullPlans = await mysqlQuery(
//             `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
//             [user_id, weekStart, weekEnd]
//         );
//         return await formatAndSendPlans(weekFullPlans, res);

//     } catch (err) {
//         console.error('基于食材生成周计划出错', err);
//         return res.status(500).json({ error: '服务端出错' });
//     }
// });

router.post('/week-plan/check', async function checkWeekPlan(req, res) {
    try {
        // 1. 参数获取和验证
        const { user_id, base_date } = req.body;

        if (!user_id || !base_date) {
            return res.send({
                code: 5,
                error: '缺少 user_id 或 base_date 参数',
                message: '请提供用户ID和基准日期'
            });
        }

        console.log('[查询参数] user_id:', user_id, 'base_date:', base_date);

        // 2. 验证用户是否存在
        const userRows = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id=?', [user_id]);
        if (!userRows || userRows.length === 0) {
            return res.send({
                code: 4,
                error: '用户不存在',
                message: '未找到指定用户'
            });
        }

        // 3. 计算 base_date 所在自然周的周一~周日
        const baseMoment = moment(base_date, 'YYYY-MM-DD');

        // 验证日期格式
        if (!baseMoment.isValid()) {
            return res.send({
                code: 3,
                error: '日期格式错误',
                message: '请使用 YYYY-MM-DD 格式的日期'
            });
        }

        const weekStart = baseMoment.clone().isoWeekday(1).format('YYYY-MM-DD');
        const weekEnd = baseMoment.clone().isoWeekday(7).format('YYYY-MM-DD');

        console.log('[查询周期] 周一:', weekStart, '周日:', weekEnd);

        // 4. 查询本周是否已有计划
        const weekExistPlans = await mysqlQuery(
            `SELECT COUNT(*) as plan_count FROM Recipe_week_plan 
             WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
            [user_id, weekStart, weekEnd]
        );

        const planCount = weekExistPlans[0]?.plan_count || 0;
        console.log('[查询结果] 本周计划数量:', planCount);

        // 5. 返回结果
        if (planCount > 0) {
            // 有计划，返回 code: 0
            return res.send({
                code: 0,
                message: '本周已有饮食计划'
            });
        } else {
            // 没有计划，返回 code: 1
            return res.send({
                code: 1,
                message: '本周暂无饮食计划'
            });
        }

    } catch (err) {
        console.error('查询周计划出错', err);
        return res.send({
            code: 500,
            error: '服务端出错',
            message: '查询过程中发生错误，请稍后重试'
        });
    }
});

router.post('/week-plan/generate', async function generateWeekPlan(req, res) {
    try {
        // 1. 参数预处理
        let { user_id, base_date, meal_times = '', min_servings, max_servings, ingredients = '' } = req.body;
        if (typeof meal_times === 'string') {
            meal_times = meal_times.split(',').map(i => i.trim()).filter(Boolean);
        }
        if (!user_id || !base_date || meal_times.length === 0) {
            return res.status(400).json({ error: '缺少 user_id/base_date/meal_times 参数' });
        }

        // 2. 处理食材为数组（兼容中文逗号）
        let ingredientsArray = [];
        if (ingredients) {
            if (typeof ingredients === 'string') {
                ingredientsArray = ingredients.replace(/，/g, ',').split(',').map(s => s.trim()).filter(Boolean);
            } else if (Array.isArray(ingredients)) {
                ingredientsArray = ingredients.join(',').replace(/，/g, ',').split(',').map(s => s.trim()).filter(Boolean);
            }
        }

        // 3. 获取用户信息
        const userRows = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id=?', [user_id]);
        if (!userRows || userRows.length === 0) {
            return res.status(404).json({ error: '未找到用户' });
        }
        const user = userRows[0];

        // 4. 计算当前属于哪一"自然周"（本周日~本周六）
        // 2025-06-13(周五)所属周是2025-06-08~2025-06-14
        const baseMoment = moment(base_date, 'YYYY-MM-DD');
        // 获取本周期的周日
        const weekStart = baseMoment.clone().subtract(baseMoment.day(), 'days').format('YYYY-MM-DD'); // .day(0)是周日
        const weekEnd = moment(weekStart, 'YYYY-MM-DD').add(6, 'days').format('YYYY-MM-DD'); // 周六

        // 5. 查本周期数据，如果已有直接返回全周期数据
        const weekExistPlans = await mysqlQuery(
            `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
            [user_id, weekStart, weekEnd]
        );
        if (weekExistPlans.length > 0) {
            return await formatAndSendPlans(weekExistPlans, res);
        }

        // 6. base_date是今天以前的，不允许新建
        const todayStr = moment().format('YYYY-MM-DD');
        if (moment(base_date).isBefore(todayStr)) {
            return res.send({ code: 0, data: {} });
        }

        // 7. 只生成 base_date ~ weekEnd 的日期（周五只会生成周五和周六）
        const weekDates = [];
        let cursor = moment(base_date, 'YYYY-MM-DD');
        while (cursor.format('YYYY-MM-DD') <= weekEnd) {
            weekDates.push(cursor.format('YYYY-MM-DD'));
            cursor.add(1, 'day');
        }

        // 8. (用户偏好、份量等参数解析)
        const cuisine_like = safeParseArr(user.cuisine);
        const cuisine_dislike = safeParseArr(user.cuisine_dislike);
        const cuisine_left = safeParseArr(user.cuisine_left);
        const allergies = safeParseArr(user.allergies);

        const userMinServings = user.min_servings;
        const userMaxServings = user.max_servings;
        let dinnerMinServings = (min_servings !== undefined && min_servings !== null)
            ? Number(min_servings)
            : (userMinServings !== undefined && userMinServings !== null ? Number(userMinServings) : 3);
        let dinnerMaxServings = (max_servings !== undefined && max_servings !== null)
            ? Number(max_servings)
            : (userMaxServings !== undefined && userMaxServings !== null ? Number(userMaxServings) : null);

        // 9. 开始料理分配
        const planToInsert = [];
        for (let meal of meal_times) {
            const daysN = weekDates.length;
            const receNeed = (meal === 'dinner') ? daysN * 2 : daysN;

            // 建where条件
            let whereBase = '1=1 ';
            if (cuisine_dislike.length > 0) {
                whereBase += 'AND NOT (' +
                    cuisine_dislike.map(cd => `JSON_CONTAINS(cuisine, '"${cd}"')`).join(' OR ') +
                    ') ';
            }
            if (allergies.length > 0) {
                whereBase += 'AND (allergies IS NULL ' +
                    'OR ' +
                    allergies.map(alg => `NOT JSON_CONTAINS(allergies, '"${alg}"')`).join(' AND ') +
                    ') ';
            }
            let whereCuisineLike = '';
            if (cuisine_like.length > 0) {
                whereCuisineLike = 'AND (' +
                    cuisine_like.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') +
                    ') ';
            } else if (cuisine_left.length > 0) {
                whereCuisineLike = 'AND (' +
                    cuisine_left.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') +
                    ') ';
            }
            let extraWhere = '';
            if (meal === 'breakfast') {
                extraWhere += 'AND (total_mins IS NULL OR total_mins <= 20)';
            } else if (meal === 'lunch') {
                extraWhere += 'AND (total_mins BETWEEN 10 AND 30)';
            } else if (meal === 'dinner') {
                extraWhere += 'AND (total_mins >= 30)';
                if (!isNaN(dinnerMinServings)) {
                    extraWhere += ` AND max_servings >= ${dinnerMinServings}`;
                }
                if (dinnerMaxServings !== null && !isNaN(dinnerMaxServings)) {
                    extraWhere += ` AND max_servings <= ${dinnerMaxServings}`;
                }
            }

            let sqlWhere = whereBase + whereCuisineLike + extraWhere;
            let recipeSql = `SELECT * FROM Recipes WHERE ${sqlWhere}`;
            const recipes = await mysqlQuery(recipeSql, []);

            // 按食材过滤
            let filteredRecipes = recipes;
            if (ingredientsArray.length > 0) {
                filteredRecipes = recipes.filter(row => {
                    let recipeIngredients = [];
                    try {
                        recipeIngredients = JSON.parse(row.ingredients || '[]');
                    } catch { }
                    let matchedSet = new Set();
                    if (Array.isArray(recipeIngredients)) {
                        for (const userIngredient of ingredientsArray) {
                            const userIngredientSafe = userIngredient.trim().toLowerCase();
                            if (!userIngredientSafe) continue;
                            const matched = recipeIngredients.some(ingredient => {
                                let ingredientName = '';
                                if (typeof ingredient === 'string') {
                                    ingredientName = ingredient;
                                } else if (ingredient && typeof ingredient === 'object') {
                                    ingredientName = ingredient.name || ingredient.ingredient || '';
                                }
                                return ingredientName && ingredientName.toLowerCase().includes(userIngredientSafe);
                            });
                            if (matched) matchedSet.add(userIngredientSafe);
                        }
                    }
                    // 至少2种食材
                    return matchedSet.size >= 2;
                });
            }

            // 随机分配（数量不够全部上）
            let randomRecipes = [];
            if (filteredRecipes.length > receNeed) {
                const arr = [...filteredRecipes];
                for (let i = 0; i < receNeed; i++) {
                    const idx = Math.floor(Math.random() * arr.length);
                    randomRecipes.push(arr[idx]);
                    arr.splice(idx, 1);
                }
            } else {
                randomRecipes = filteredRecipes;
            }

            // 均匀分配到各天
            const dailyArr = Array(daysN).fill(0).map(() => []);
            let tmpRecipes = [...randomRecipes];
            for (let i = 0; i < receNeed && tmpRecipes.length > 0; i++) {
                let dayIdx = i % daysN;
                if (meal === 'dinner') {
                    if (dailyArr[dayIdx].length >= 2) {
                        for (let di = 0; di < daysN; di++) {
                            if (dailyArr[di].length < 2) { dayIdx = di; break; }
                        }
                    }
                } else {
                    if (dailyArr[dayIdx].length >= 1) {
                        for (let di = 0; di < daysN; di++) {
                            if (dailyArr[di].length < 1) { dayIdx = di; break; }
                        }
                    }
                }
                const randIdx = Math.floor(Math.random() * tmpRecipes.length);
                const recipe = tmpRecipes[randIdx];
                dailyArr[dayIdx].push(recipe);
                tmpRecipes.splice(randIdx, 1);
            }
            for (let i = 0; i < daysN; i++) {
                for (let r of dailyArr[i]) {
                    if (r) {
                        planToInsert.push({
                            id: utils.createId(),
                            user_id: user_id,
                            recipe_id: r.id,
                            meal_date: weekDates[i],
                            meal_time: meal
                        });
                    }
                }
            }
        }

        // 10. 插入所有新计划
        if (planToInsert.length > 0) {
            const insertSql = `
                INSERT INTO Recipe_week_plan 
                  (id, user_id, recipe_id, meal_date, meal_time)
                VALUES ${planToInsert.map(() => '(?,?,?,?,?)').join(',')}
            `;
            const params = planToInsert.flatMap(item =>
                [item.id, item.user_id, item.recipe_id, item.meal_date, item.meal_time]
            );
            await mysqlQuery(insertSql, params);
        }

        // 11. 查询并返回整个周周期数据
        const weekFullPlans = await mysqlQuery(
            `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date>=? AND meal_date<=?`,
            [user_id, weekStart, weekEnd]
        );
        return await formatAndSendPlans(weekFullPlans, res);

    } catch (err) {
        console.error('生成周计划出错', err);
        return res.status(500).json({ error: '服务端出错' });
    }
});

//周计划相似菜谱推荐
router.post('/week-plan/similar-recipes', async function (req, res) {
    try {
        // 1. 参数校验 - recipe_id改为可选参数
        let { user_id, recipe_id, base_date, meal_times, min_servings, max_servings } = req.body;
        if (!user_id || !base_date || !meal_times) {
            return res.status(400).json({ error: '参数缺失：user_id, base_date, meal_times为必填' });
        }
        const meal = meal_times;
        console.log('[入口参数]', { user_id, recipe_id: recipe_id || '无', base_date, meal, min_servings, max_servings });

        // 2. 用户信息
        const userRows = await mysqlQuery('SELECT * FROM Recipe_user WHERE user_id=?', [user_id]);
        if (!userRows || userRows.length === 0) {
            return res.status(404).json({ error: '用户不存在' });
        }
        const user = userRows[0];

        // 3. 原菜谱信息获取（可选）
        let recipeToReplace = null;
        if (recipe_id) {
            const recipeRows = await mysqlQuery('SELECT * FROM Recipes WHERE id=?', [recipe_id]);
            if (!recipeRows || recipeRows.length === 0) {
                return res.status(404).json({ error: '待替换菜谱ID不存在' });
            }
            recipeToReplace = recipeRows[0];
        }

        // 4. 同天同餐已安排（排除本菜和其他已安排菜）
        const planRows = await mysqlQuery(
            'SELECT recipe_id FROM Recipe_week_plan WHERE user_id=? AND meal_date=? AND meal_time=?',
            [user_id, base_date, meal]
        );
        const existRecipeIds = planRows.map(r => r.recipe_id);
        console.log('[当天本餐排除菜谱ID/已有计划]:', existRecipeIds);

        // 5. 食材分词处理（仅在传入recipe_id时执行）
        let tokens = []; // 默认为空数组
        let rawIngredientsArr = [];

        if (recipeToReplace) {
            // 原菜食材 ingredients 分词，变成关键token数组
            function tokenizeIngredients(arr) {
                let tokens = [];
                arr.forEach(str => {
                    str.replace(/[®()]/g, '')
                        .split(/[,，]/)
                        .map(s => s.trim().toLowerCase())
                        .forEach(segment => {
                            segment.split(/\s+/).forEach(t => {
                                // 长度大于2的避免无意义token
                                if (t.length > 2) tokens.push(t);
                            });
                        });
                });
                return Array.from(new Set(tokens));
            }

            // 解析原菜食材字段ingredients生成原始成分短语
            try {
                const ings = JSON.parse(recipeToReplace.ingredients || '[]');
                for (const obj of ings) {
                    if (typeof obj === 'string') rawIngredientsArr.push(obj.trim().toLowerCase());
                    else if (typeof obj === 'object') {
                        let name = obj.ingredient || obj.name || '';
                        if (name) rawIngredientsArr.push(name.trim().toLowerCase());
                    }
                }
                rawIngredientsArr = Array.from(new Set(rawIngredientsArr));
            } catch (e) { }

            // 再分词
            tokens = tokenizeIngredients(rawIngredientsArr);
            if (tokens.length === 0) {
                console.warn('[原菜谱未能分解出有效食材，将返回通用推荐]', recipeToReplace.ingredients);
            }
            console.log('[原菜配料分词结果 tokens]', tokens);
        } else {
            console.log('[未传入recipe_id，将返回通用推荐]');
        }

        // 6. 用户口味/过敏等偏好
        const cuisine_like = safeParseArr(user.cuisine);
        const cuisine_dislike = safeParseArr(user.cuisine_dislike);
        const cuisine_left = safeParseArr(user.cuisine_left);
        const allergies = safeParseArr(user.allergies);
        const userMinServings = user.min_servings;
        const userMaxServings = user.max_servings;
        let dinnerMinServings = (min_servings != null) ? Number(min_servings)
            : (userMinServings != null) ? Number(userMinServings) : 3;
        let dinnerMaxServings = (max_servings != null) ? Number(max_servings)
            : (userMaxServings != null) ? Number(userMaxServings) : null;

        // 7. SQL拼装
        let whereBase = '1=1 ';
        if (cuisine_dislike.length > 0) {
            whereBase += 'AND NOT (' +
                cuisine_dislike.map(cd => `JSON_CONTAINS(cuisine, '"${cd}"')`).join(' OR ') + ') ';
        }
        if (allergies.length > 0) {
            whereBase += 'AND (allergies IS NULL ' +
                'OR ' +
                allergies.map(alg => `NOT JSON_CONTAINS(allergies, '"${alg}"')`).join(' AND ') + ') ';
        }
        let whereCuisineLike = '';
        if (cuisine_like.length > 0) {
            whereCuisineLike = 'AND (' +
                cuisine_like.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') + ') ';
        } else if (cuisine_left.length > 0) {
            whereCuisineLike = 'AND (' +
                cuisine_left.map(cl => `JSON_CONTAINS(cuisine, '"${cl}"')`).join(' OR ') + ') ';
        }
        let extraWhere = '';
        if (meal === 'breakfast') {
            extraWhere += 'AND (total_mins IS NULL OR total_mins <= 20)';
        } else if (meal === 'lunch') {
            extraWhere += 'AND (total_mins BETWEEN 10 AND 30)';
        } else if (meal === 'dinner') {
            extraWhere += 'AND (total_mins >= 30)';
            if (!isNaN(dinnerMinServings)) {
                extraWhere += ` AND max_servings >= ${dinnerMinServings}`;
            }
            if (dinnerMaxServings !== null && !isNaN(dinnerMaxServings)) {
                extraWhere += ` AND max_servings <= ${dinnerMaxServings}`;
            }
        }

        // 排除当天同餐已有菜谱，如果传了recipe_id也排除目标菜
        const blockIds = [...existRecipeIds];
        if (recipe_id) {
            blockIds.push(recipe_id);
        }
        let avoidRepeat = '';
        if (blockIds.length > 0) {
            avoidRepeat = `AND id NOT IN (${blockIds.map(() => '?').join(',')})`;
        }
        let sqlWhere = whereBase + whereCuisineLike + extraWhere + ' ' + avoidRepeat;
        let sqlParams = blockIds;
        let recipeSql = `SELECT * FROM Recipes WHERE ${sqlWhere} LIMIT 2000`;
        const recipes = await mysqlQuery(recipeSql, sqlParams);
        console.log('[可选菜池数量]（filter前）:', recipes.length);

        // 8. 食材匹配筛选（仅在有tokens时进行）
        let finalList = [];
        if (tokens.length > 0) {
            // 有食材token时，进行模糊分词匹配，结果必须有2个token命中recipeIngredients任意成分
            for (const row of recipes) {
                let recipeIngredients = [];
                try {
                    recipeIngredients = JSON.parse(row.ingredients || '[]');
                } catch { }
                let matchedSet = new Set();
                if (Array.isArray(recipeIngredients)) {
                    for (const token of tokens) {
                        const matched = recipeIngredients.some(ingredient => {
                            let ingredientName = '';
                            if (typeof ingredient === 'string') {
                                ingredientName = ingredient;
                            } else if (typeof ingredient === 'object') {
                                ingredientName = ingredient.name || ingredient.ingredient || '';
                            }
                            return ingredientName && ingredientName.toLowerCase().includes(token);
                        });
                        if (matched) matchedSet.add(token);
                    }
                }
                if (matchedSet.size >= 2) {
                    finalList.push(row);
                }
            }
            console.log('[最终命中2+食材token的数量]:', finalList.length);
        } else {
            // 没有食材token时，直接使用所有符合基础条件的菜谱
            finalList = recipes;
            console.log('[通用推荐菜谱数量]:', finalList.length);
        }

        // 最多返回15条
        finalList = finalList.slice(0, 15);

        if (finalList.length === 0) {
            console.warn('[推荐结果为空] 可能筛选条件过于严格或菜谱库不足');
        }

        // 9. 格式化和返回
        const formatted = finalList.map(row => {
            let cuisine = [], dietary_preference = [], ingredients = [], steps = [], allergies = [];
            try { cuisine = JSON.parse(row.cuisine || '[]'); } catch { }
            try { dietary_preference = JSON.parse(row.dietary_preference || '[]'); } catch { }
            try { ingredients = JSON.parse(row.ingredients || '[]'); } catch { }
            try { allergies = JSON.parse(row.allergies || '[]'); } catch { }
            try { steps = JSON.parse(row.steps || '[]'); } catch { }

            // matched_ingredients计算：有tokens时计算匹配度，没有时返回空数组
            let matched_ingredients = [];
            if (tokens.length > 0) {
                matched_ingredients = tokens.filter(ai =>
                    ingredients.some(obj =>
                        (typeof obj === 'string' && obj.includes(ai)) ||
                        (typeof obj === 'object' && ((obj.name && obj.name.includes(ai)) || (obj.ingredient && obj.ingredient.includes(ai))))
                    )
                );
            }

            return {
                id: row.id,
                english_name: row.english_name || '',
                translated_name: row.translated_name || '',
                flavor: row.flavor || '',
                cuisine,
                dietary_preference,
                meal_time: row.meal_time ? row.meal_time.split(',') : [],
                categories: row.categories ? row.categories.split(',') : [],
                cooking_effort: row.cooking_effort ? row.cooking_effort.split(',') : [],
                ingredients,
                allergies,
                total_mins: row.total_mins || 0,
                min_servings: row.min_servings || 0,
                max_servings: row.max_servings || 0,
                prep_mins: row.prep_mins || 0,
                cook_mins: row.cook_mins || 0,
                dish_presentation_description: row.dish_presentation_description
                    ? row.dish_presentation_description.split(',') : [],
                protein_g: row.protein_g,
                fat_g: row.fat_g,
                carbs_g: row.carbs_g,
                calories: row.calories || 0,
                steps,
                matched_ingredients, // 有食材匹配时显示匹配的食材，没有时为空数组
                cuisine_match: row.cuisine_match || 0,
                diet_match: row.diet_match || 0,
                created_at: row.created_at,
                updated_at: row.updated_at,
                img_URL: row.img_URL
            };
        });
        return res.send({ code: 0, data: formatted });

    } catch (err) {
        console.error('周计划菜谱推荐出错', err);
        return res.status(500).json({ error: '服务端出错' });
    }
});
 
//添加
router.post('/week-plan/add-recipe', async (req, res) => {
    const { user_id, recipe_id, base_date, meal_time } = req.body;
    if (!user_id || !recipe_id || !base_date || !meal_time) {
        return res.status(400).json({ error: '参数缺失' });
    }
    // 查同天同餐已有计划，复制 created_at/updated_at
    const groupRows = await mysqlQuery(
        `SELECT created_at,updated_at FROM Recipe_week_plan WHERE user_id=? AND meal_date=? AND meal_time=? LIMIT 1`,
        [user_id, base_date, meal_time]
    );
    let created_at = null, updated_at = null;
    if (groupRows && groupRows.length > 0) {
        created_at = groupRows[0].created_at;
        updated_at = groupRows[0].updated_at;
    }
    const id = utils.createId();
    await mysqlQuery(
        `INSERT INTO Recipe_week_plan (id,user_id,recipe_id,meal_date,meal_time,created_at,updated_at)
         VALUES (?,?,?,?,?,?,?)`,
        [id, user_id, recipe_id, base_date, meal_time, created_at, updated_at]
    );
    // 返回该天该餐所有计划（含食谱详情）
    const list = await getDayMealPlanWithDetail(user_id, base_date, meal_time);
    return res.send({ code: 0, data: list });
});
//替换
router.post('/week-plan/replace-recipe', async (req, res) => {
    const { user_id, old_recipe_id, recipe_id, base_date, meal_time } = req.body;
    if (!user_id || !old_recipe_id || !recipe_id || !base_date || !meal_time) {
        return res.status(400).json({ error: '参数缺失' });
    }
    // 查原记录，保留 created_at/updated_at
    const originRows = await mysqlQuery(
        `SELECT id,created_at,updated_at FROM Recipe_week_plan WHERE user_id=? AND meal_date=? AND meal_time=? AND recipe_id=? LIMIT 1`,
        [user_id, base_date, meal_time, old_recipe_id]
    );
    if (!originRows || originRows.length === 0) {
        return res.status(404).json({ error: '未找到原菜谱计划' });
    }
    const { id: originId, created_at, updated_at } = originRows[0];

    // 用 update 替换
    await mysqlQuery(
        `UPDATE Recipe_week_plan SET recipe_id=? ,updated_at=? WHERE id=?`,
        [recipe_id, updated_at, originId]
    );
    // 返回该天该餐所有计划
    const list = await getDayMealPlanWithDetail(user_id, base_date, meal_time);
    return res.send({ code: 0, data: list });
});
//删除
router.post('/week-plan/delete-recipe', async (req, res) => {
    const { user_id, recipe_id, base_date, meal_time } = req.body;
    if (!user_id || !recipe_id || !base_date || !meal_time) {
        return res.status(400).json({ error: '参数缺失' });
    }
    await mysqlQuery(
        `DELETE FROM Recipe_week_plan WHERE user_id=? AND meal_date=? AND meal_time=? AND recipe_id=?`,
        [user_id, base_date, meal_time, recipe_id]
    );
    // 返回该天该餐所有计划
    const list = await getDayMealPlanWithDetail(user_id, base_date, meal_time);
    return res.send({ code: 0, data: list });
});
// 安全解析数组字段的工具
function safeParseArr(val) {
    if (!val) return [];
    if (Array.isArray(val)) return val;
    if (typeof val === 'string') {
        let s = val.trim();
        try {
            if (s.startsWith('[') || s.startsWith('{')) {
                let parsed = JSON.parse(s);
                return Array.isArray(parsed) ? parsed : [parsed];
            }
            // 必须把逗号分隔的也split掉！
            if (s.indexOf(',') !== -1) {
                return s.split(',').map(t => t.trim()).filter(Boolean);
            }
            return s ? [s] : [];
        } catch (e) {
            return s ? [s] : [];
        }
    }
    return [];
}
// ========== 数据格式化+调试打印 ==========
async function formatAndSendPlans(weekPlanRows, res) {
    console.log('[format] weekPlanRows数量:', weekPlanRows.length); 

    if (!weekPlanRows || weekPlanRows.length === 0) {
        return res.send({code:0, data: {} });
    }

    const recipeIds = Array.from(new Set(weekPlanRows.map(r => r.recipe_id)));
    if (recipeIds.length === 0) {
        console.warn('[菜谱ID为空]', weekPlanRows);
        return res.send({ code: 0, data: {} });
    }
    const recipePlaceholders = recipeIds.map(() => '?').join(',');
    const recipeRows = await mysqlQuery(
        `SELECT * FROM Recipes WHERE id IN (${recipePlaceholders})`, recipeIds
    );
    console.log('[Recipe查得]', recipeRows.map(r => r.id));

    const recipeMap = {};
    for (let r of recipeRows) recipeMap[r.id] = r;

    // --- 按 meal_date → 三餐 结构组织 ---
    const data = {};
    for (const item of weekPlanRows) {
        const meal_time = item.meal_time;
        let meal_date = item.meal_date;
        meal_date = moment(meal_date).format('YYYY-MM-DD');
        if (!data[meal_date]) {
            data[meal_date] = { breakfast: [], lunch: [], dinner: [] };
        }
        const row = recipeMap[item.recipe_id] || {};
        let cuisine = [], dietary_preference = [], ingredients = [], steps = [], allergies = [];
        try { cuisine = JSON.parse(row.cuisine || '[]'); } catch { }
        try { dietary_preference = JSON.parse(row.dietary_preference || '[]'); } catch { }
        try { ingredients = JSON.parse(row.ingredients || '[]'); } catch { }
        try { allergies = JSON.parse(row.allergies || '[]'); } catch { }
        try { steps = JSON.parse(row.steps || '[]'); } catch { }

        data[meal_date][meal_time].push({
            meal_date,
            daily_recommend_created_at: item.created_at,
            id: item.recipe_id,
            english_name: row.english_name || '',
            translated_name: row.translated_name || '',
            flavor: row.flavor || '',
            cuisine,
            dietary_preference,
            meal_time: [meal_time],
            categories: row.categories ? row.categories.split(',') : [],
            cooking_effort: row.cooking_effort ? row.cooking_effort.split(',') : [],
            ingredients,
            allergies,
            total_mins: row.total_mins || 0,
            min_servings: row.min_servings || 0,
            max_servings: row.max_servings || 0,
            prep_mins: row.prep_mins || 0,
            cook_mins: row.cook_mins || 0,
            dish_presentation_description: row.dish_presentation_description
                ? row.dish_presentation_description.split(',') : [],
            protein_g: row.protein_g,
            fat_g: row.fat_g,
            carbs_g: row.carbs_g,
            calories: row.calories || 0,
            steps,
            matched_ingredients: row.matched_ingredients ? row.matched_ingredients.split(',') : [],
            cuisine_match: row.cuisine_match || 0,
            diet_match: row.diet_match || 0,
            created_at: row.created_at,
            updated_at: row.updated_at,
            img_URL: row.img_URL
        });
    }

    console.log('[format] 最终分组data.keys:', Object.keys(data), '每个key下数据:', Object.values(data).map(day => day.breakfast.length + day.lunch.length + day.dinner.length));
    return res.send({ code: 0, data });
}
//工具：获取某天某餐所有菜谱（附带食谱详情）
async function getDayMealPlanWithDetail(user_id, meal_date, meal_time) {
    // 1. 查 Recipe_week_plan（保持顺序优先created_at,updated_at）
    const weekPlans = await mysqlQuery(
        `SELECT * FROM Recipe_week_plan WHERE user_id=? AND meal_date=? AND meal_time=? ORDER BY created_at,updated_at`,
        [user_id, meal_date, meal_time]
    );
    if (weekPlans.length === 0) return [];

    // 2. 拉取相关菜谱详情
    const recipeIds = Array.from(new Set(weekPlans.map(r => r.recipe_id)));
    const recipePlaceholders = recipeIds.map(() => '?').join(',');
    const recipeRows = await mysqlQuery(
        `SELECT * FROM Recipes WHERE id IN (${recipePlaceholders})`, recipeIds
    );
    const recipeMap = {};
    for (let r of recipeRows) recipeMap[r.id] = r;

    // 3. 合成
    return weekPlans.map(item => {
        const row = recipeMap[item.recipe_id] || {};
        let cuisine = [], dietary_preference = [], ingredients = [], steps = [], allergies = [];
        try { cuisine = JSON.parse(row.cuisine || '[]'); } catch { }
        try { dietary_preference = JSON.parse(row.dietary_preference || '[]'); } catch { }
        try { ingredients = JSON.parse(row.ingredients || '[]'); } catch { }
        try { allergies = JSON.parse(row.allergies || '[]'); } catch { }
        try { steps = JSON.parse(row.steps || '[]'); } catch { }
        return {
            meal_date: item.meal_date,
            created_at: item.created_at,
            updated_at: item.updated_at,
            id: item.recipe_id,
            english_name: row.english_name || '',
            translated_name: row.translated_name || '',
            flavor: row.flavor || '',
            cuisine,
            dietary_preference,
            meal_time_detail: row.meal_time ? row.meal_time.split(',') : [],
            categories: row.categories ? row.categories.split(',') : [],
            cooking_effort: row.cooking_effort ? row.cooking_effort.split(',') : [],
            ingredients,
            allergies,
            total_mins: row.total_mins || 0,
            min_servings: row.min_servings || 0,
            max_servings: row.max_servings || 0,
            prep_mins: row.prep_mins || 0,
            cook_mins: row.cook_mins || 0,
            dish_presentation_description: row.dish_presentation_description
                ? row.dish_presentation_description.split(',') : [],
            protein_g: row.protein_g,
            fat_g: row.fat_g,
            carbs_g: row.carbs_g,
            calories: row.calories || 0,
            steps,
            cuisine_match: row.cuisine_match || 0,
            diet_match: row.diet_match || 0,
            img_URL: row.img_URL
        };
    });
}
async function mysqlQuery(sql, arr) {
    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
module.exports = router;